Re: More of the ongoing battle with dbms_sql

From: Leif Klepp <Leif.Klepp_at_ac.com>
Date: 1996/09/14
Message-ID: <323B1343.2CF0_at_ac.com>#1/1

Brian Martin wrote:
>
> Okay, I finally dbms_sql running. ...
 [snip]
> I want to execute a procedure in my passed statement, but
> I get ye olde error: ORA-06512 and ORA-0900. Woohoo.
>
> I know I can't run execute from inside, but why not a
> procedure?
>

The following code runs with no problem in Oracle 7.1 Workgroups Server on NT 3.51.

Just type 'execute bcm.exec' on the SQL+ command line .. (after creating the package of course.. :)

create or replace package bcm as

   procedure exec;
   procedure dummy;
end bcm;
/
show errors;

create or replace package body bcm as

procedure exec is

        mcursor integer;
        mstring varchar2(80);
        mreturn integer;

begin

   mcursor := dbms_sql.open_cursor;

   mstring := 'BEGIN bcm.dummy; END;'; /* le prog to run */
   /* NOTE!!   ^^^^^ Procedures should be called in a block. */

   dbms_sql.parse(mcursor, mstring, dbms_sql.v7);    mreturn := dbms_sql.execute(mcursor);    dbms_sql.close_cursor(mcursor);
end exec;

procedure dummy is
foo integer;
begin

   select 1 into foo from dual;
end dummy;

end bcm;
/

Best of luck,
/Leif Klepp Received on Sat Sep 14 1996 - 00:00:00 CEST