Re: More of the ongoing battle with dbms_sql
Date: 1996/09/20
Message-ID: <1996Sep20.080201.31493_at_ludens>#1/1
hello
In article <3236E81F.2FF0_at_ix.netcom.com>, Brian Martin <icbkr_at_ix.netcom.com> writes:
> Okay, I finally dbms_sql running. Never, and I mean *never*
> name a variable "return". You wouldn't believe the hoops
> you jump through trying to trace *that* one down. Why
> can't Oracle pick up on the use of reserved words like
> other compilers? Anyway, back to dbms_sql.
>
> 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?
>
> To run the below stuff, I'm at a SQL> prompt and
> doing "execute exec;"
> I get the above error messages, which indicates
> it's barfing on the procedure, but there MUST BE
> A WAY!!!
>
> Here's the example code:
>
>>>>>>>>>>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 := 'bcm.dummy'; /* le prog to run */
try
msting := 'BEGIN BCM.DUMMY; END';
it WILL work
> 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 count(*) into foo from tab;
> end dummy;
>
> end bcm;
> /
> show errors;
THT
peter
Received on Fri Sep 20 1996 - 00:00:00 CEST