Re: dynamic procedure calls in PL/SQL

Try dbms_sql since execute immediate is not supported in 7.3. (You should upgrade). You need to get a handle then execute your procedure as a string with begin and end.

e.g.
myProc varchar2(2000);
myHandle ...
begin
myHandle:=dbms_sql....;
myProc:='begin; myProc( args ); end;';
dbms_sql.execute(myHandle,myProc);

or something like that. You are basically going to have to construct a string that dbms_sql can execute.I don't have all my documentation in front of me or I would fill in the details a little bit more. Jim

"Tom" <tptnll_at_yahoo.com> wrote in message news:2e7bb509.0108311557.30cfb03a_at_posting.google.com...
> Hi Again:
> The version of Oracle is 7.3.4.
> We have tried "execute immediate" and "dbms_sql" without success.
> They work with a standard sql statements like a "select" but they do
> not seem to "understand" the procedure name. The compile itself fails.
> Thanks again
>
> "Jim Kennedy" <kennedy-family_at_home.com> wrote in message
news:<2LQj7.444138$p33.8508133_at_news1.sttls1.wa.home.com>...
> > Look at docs under "execute immediate' or dbms_sql package.
> > Good to tell us what version.
> > Jim
> > "Tom" <tptnll_at_yahoo.com> wrote in message
> > news:2e7bb509.0108310953.3ec4a8bd_at_posting.google.com...
> > > Hi:
> > > I have a pl/sql procedure that needs to be able to call any one of
> > > many other procedures. The names of these procedures are carried in a
> > > table, and depending on the current circumstance, the appropriate
> > > procedure name is selected and then executed. Is there a way to
> > > dynamically execute these procedures?
> > >
> > > The psuedo looks like this:
> > >
> > > CREATE OR REPLACE PROCEDURE pr_tom(an_circumstance number,
> > > otherarguements)
> > > AS
> > > BEGIN
> > > select procedure_name
> > > into variable
> > > from table
> > > where circumstance := an_circumstance;
> > >
> > > /* Logically, this is what I want to do
> > > ******************************
> > >
> > > execute variable(otherarguements) /* the arguement list is always the
> > > same */
> > >
> > > *********************************** Can this be done? */
> > >
> > > END pr_tom;
> > >
> > >
> > > Thanks in advance
Received on Fri Aug 31 2001 - 19:22:23 CDT