Re: Executing DDL commands from within a Begin-Select

[Wayne Perry <Wayne.PERRY@EDDEPT.WA.EDU.AU>]

Tim,

I took your code and changed it a bit...   try this.

! example sql update statement

let $p_Expr = 'update ps_epl_tx_dtls_aus set tax_file_number = ' || '''' ||
              '123456789' || '''' || ' where emplid = ' || '''' || '0140143'
|| ''''

show 'Expr = ' $p_Expr

begin-sql
Declare
cid integer;;
ret integer;;
begin
cid := DBMS_SQL.OPEN_CURSOR;;
DBMS_SQL.PARSE(cid, $p_expr, dbms_sql.v7 );;
ret:= DBMS_SQL.execute(cid);;
DBMS_SQL.CLOSE_CURSOR( cid );;
end;;
end-sql


You needed a begin/end around the pl/sql block and I removed the [] brackets
around the expression.  I assume you are going to execute the query so you
need a  return code field defined and the execute statement as well

I played around with returning the columns from a query using the
define_column and column_value procedures of the DBMS_SQL package to return
the results of the query to either $ or # variables so that the SQR report
could do something with them but I couldn't get that bit to work.  The
PL/SQL compiler doesn't seem to like assigning variables to fields not set
up in the 'declare' section. The SQR manual seems to indicate that this is
ok though - maybe someone else can shed some light on this.



Tim Green wrote:

Date:    Fri, 7 May 1999 06:45:07 -0600
From:    Tim Green <Tim_Green@NAD.ADP.COM <mailto:Tim_Green@NAD.ADP.COM> >
Subject: Re: Executing DDL commands from within a Begin-Select

This is kind of what I'm trying to do now.  However, my experience with
Oracle
is limited and I'm either tripping over a syntax problem or I'm trying to do
something else that I can't.  Perhaps you can point out an obvious mistake:

         ! Now, execute the expression - it's a PL/SQL function that needs
         ! to be created.
         begin-SQL on-error=Error-Handler
            Declare cid integer;;
            cid := DBMS_SQL.OPEN_CURSOR;;
            DBMS_SQL.PARSE( cid, '[$p_expr]', dbms_sql.v7 );;
            DBMS_SQL.CLOSE_CURSOR( cid );;
         end-SQL

When this is executed, it aborts with the following error:

(SQR 5528) ORACLE OPARSE error -6550 in cursor 8:
   ORA-06550: line 1, column 26:
PLS-00103: Encountered the symbol "=" when expecting one of the following:

   constant exception <an
SQL:  Declare cid integer; cid := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE( cid,
      '[$p_expr]', dbms_sql.v7 ); DBMS_SQL.CLOSE_CURSOR( cid );
Error at:  :=

Error on line 1762:
   (SQR 3716) Error in SQL statement.

I stole this code snippet from an example in the Oracle documentation.  I
suspect (hope) that I'm just doing something stupid.

Thanks,
Tim