Re: Executing DDL commands from within a Begin-Select

["Fay, Brannon" <brannon.fay@PAETEC.COM>]

Check that you have access to sys.dbms_sql package and/or create function
privileges???  Oh yeah, a problem I ran into - these privileges must be set
specifically to the user, not a role that is assigned to the user.  PL/SQL
is run behind the role level of user access.  That might help...

-----Original Message-----
From: Tim Green [mailto:Tim_Green@NAD.ADP.COM]
Sent: Friday, May 07, 1999 10:45 AM
To: Multiple recipients of list SQR-USERS
Subject: Re: Executing DDL commands from within a Begin-Select


Well, I'm almost there.  Thanks for the help.  I hate being a rookie again!

According to the Oracle documentation, the EXECUTE command isn't necessary
as
the PARSE command parses and executes DDL statements.  There examples show
this
also.

However, using the following code:

         begin-SQL on-error=Error-Handler
            Declare
               cid integer;;
!               dummy integer;;
            Begin
               cid := DBMS_SQL.OPEN_CURSOR;;
               DBMS_SQL.PARSE( cid, $p_expr, dbms_sql.v7 );;
!               dummy := DBMS_SQL.EXECUTE( cid );;
               DBMS_SQL.CLOSE_CURSOR( cid );;
            End;;
         end-SQL

I'm getting the following error (whether I have the EXECUTE statement
commented
out or not):

#sql-status: -24344.000000
$sql-error:  ORA-24344: success with compilation error
ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at line 1

The value of $p_expr at runtime is:

Create or Replace Function SVC_Rate( p_svc number ) Return number Is Begin
 If p_svc >= 60 then return 0;
 elsif p_svc >= 48 then return 0.029;
 elsif p_svc >= 24 then return 0.049;
 elsif p_svc >= 12 then return 0.069;
 elsif p_svc >= 0 then return 0.091;
 end if;
end SVC_Rate;

I can execute this statement directly and Oracle has no errors.  Any ideas?

Thanks again,
Tim





"Fay, Brannon" <brannon.fay@PAETEC.COM> on 05/07/99 09:06:57 AM

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




 begin-SQL on-error=Error-Handler
      Declare
                cid integer;;
-->             dummy integer;;
-->     BEGIN
        cid := DBMS_SQL.OPEN_CURSOR;;
              DBMS_SQL.PARSE( cid, '[$p_expr]', dbms_sql.v7 );;
-->             dummy := DBMS_SQL.EXECUTE( cid );;
                DBMS_SQL.CLOSE_CURSOR( cid );;
-->     END;;
end-SQL

That should give you the pl/sql you want.  I don't know if you need the [ ]
around the string var or not.  I haven't used pl/sql integrated with sqr
yet, but I have struggled with dynamic sql.  I can't wait until (if) we
upgrade to 8i as dynamic sql is built in (along with a lot of other nice
features)

Brannon
-----Original Message-----
From: Tim Green [mailto:Tim_Green@NAD.ADP.COM]
Sent: Friday, May 07, 1999 8:45 AM
To: Multiple recipients of list SQR-USERS
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





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