Re: Executing DDL commands from within a Begin-Select

[Tim Green <Tim_Green@NAD.ADP.COM>]

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





Kris Narravula <kris_narravula@HOTMAIL.COM> on 05/06/99 08:48:13 PM

Please respond to SQR-USERS@list.iex.net

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Tim Green/HWRD/ASG_Louisville/NAD)
Subject:  Re: Executing DDL commands from within a Begin-Select




I would suggest using a PL/SQL block to select whatever you want to and
create the required function using the standard Oracle packages & Procedures
like DBMS_DDL and insert the records appropriately in the same PL/SQL block.

As you would be doing the selecting, creating the procedure and inserting
into other tables in the same PL/SQL block you may overcome the limitations
in SQR.

Good luck.

Kris


----- Original Message -----
From: Tim Green <Tim_Green@NAD.ADP.COM>
To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
Sent: Thursday, May 06, 1999 3:58 PM
Subject: Re: Executing DDL commands from within a Begin-Select


Unfortunately this won't work for what I'm trying to accomplish.  My program
does something like this:

Begin-Select

     Begin-SQL
     Create or Replace Function Do_Something() ...
     End-SQL

     Begin-SQL
     Insert into XXX select A, B, C, Do_Something() from YYY
     End-SQL

End-Select

The function I create varies based on data from the outer select statement.
I
can avoid using a function if I absolutely have to, I just don't want to.

Thanks though,
Tim





Steven Calvert <calvert@ULETH.CA> on 05/06/99 06:31:03 PM

Please respond to SQR-USERS@list.iex.net

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Tim Green/HWRD/ASG_Louisville/NAD)
Subject:  Re: Executing DDL commands from within a Begin-Select




I would suggest storing all of the commands in an SQR string array, then
processing
the array one record at a time. Of couse, array sizes are static, but you
probably
do not have a huge number of DDL statements anyway. Also, you may need to
consider
recovery in case the system crashes in the middle of processing the array.

Good luck!

Tim Green wrote:

> Does anyone know how I might execute a DDL command from within a
Begin-Select
> loop on Oracle?
>
> I want to generate some code (a PL/SQL function) dynamically for each row
> retrieved with a Begin-Select.  It needs to be done while the select loop
is
> running, not after it's complete.  I tried doing this by simply executing
the
> PL/SQL commands using a Begin-SQL paragraph, but it caused the
Begin-Select to
> go into an infinite loop.  After looking back through the SQR
documentation,
the
> reference guide does say that, on Oracle, DDL statements cannot be
executed
from
> within a Begin-Select loop since doing so forces a commit, which resets
the
> Begin-Select's cursor.
>
> Nevertheless, does anyone know how to do it anyway?  On Sybase or MS SQL
Server,
> I'd simply use the -Cnn parameter to force the Begin-SQL paragraph to be
on
> another connection, but apparently there is no such option with SQR on
Oracle.
> The only way I could think to do it is to write the DDL to a file and then
use
> the call system command to fire off another SQR program (or sqlplus)
that'll
> execute the DDL for me.  Unfortunately, that's a little more of a hassle
than
I
> want to deal with.
>
> Thanks in advance,
> Tim

--
Steven Calvert
calvert@uleth.ca
University of Lethbridge
(403)329-2071