Re: Executing DDL commands from within a Begin-Select

[Rajvirendra Singh <rvsingh@HOTMAIL.COM>]

Why don't you display $p_expr and make sure it is a valid sql statement.
After parsing you need to execute it. I do not know your mising here or
missing in real code too.

>From: Tim Green <Tim_Green@NAD.ADP.COM>
>Reply-To: SQR-USERS@list.iex.net
>To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
>Subject: Re: Executing DDL commands from within a Begin-Select
>Date: Fri, 7 May 1999 06:45:07 -0600
>
>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


_______________________________________________________________
Get Free Email and Do More On The Web. Visit http://www.msn.com