Re: SQR-USERS Digest - 5 May 1999 to 6 May 1999

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

Have you considered setting up your select as a cursor within the Pl/Sql
block, process the cursor with a 'for' loop and generate the DDL statements
as you go along.  I've done this in SQR a few times as you can execute
pretty tight control over what you are doing with the data and how it gets
processed.

The next bit is what I haven't tried. You may be able to use the dynamic sql
package that comes with Oracle to open another cursor and parse and execute
the statement within your PL/SQL block.

The only real hassle is changing your coding for generating the DDL
statements to be PL/SQL syntax.

If you want any samples of PL/Sql procedures that use dynamic sql with P/S
tables let me know.

Love to know if it works.

Wayne Perry


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