Re: Executing DDL commands from within a Begin-Select

[Ed Kelly <ed_kelly@HOTMAIL.COM>]

Tim:

You might try "Call System Using" to launch another SQR program that does
the DDL command.  Since this will execute a separate program, it should not
affect the cursors of the calling program.  Depending upon what you are
trying to do with your PL/SQL, this may work for you.

For example:

Let $Command = 'S:\PT701\SQRBINW\SQRW.EXE ' ||    ! Compiler
               'C:\USER\SQR\MYPROG.SQR ' ||       ! DDL Program
               'user/pswd@database ' ||           ! Connectivity Info
               '-iC:\USER\SQR\;S:\PT701\SQR\ ' || ! SQR Flags
               '-mS:\PT701\SQR\ALLMAXES.MAX '||   ! -Add additional
               '-oc:\TEMP\SQR2.LOG'               ! -flags as needed

Call System Using $Command #Rtn Wait

Hope this helps,
Ed Kelly

>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: Executing DDL commands from within a Begin-Select
>Date: Thu, 6 May 1999 16:04:48 -0600
>
>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



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