Re: dynamic procedure calls in PL/SQL

From: Tom <tptnll_at_yahoo.com>
Date: 2 Sep 2001 08:12:28 -0700
Message-ID: <2e7bb509.0109020712.66545904@posting.google.com>

Thank You Jim -- I going on vacation for the week so I won't get back

to this problem right away. We did try dbms_sql, but when it got to
the "dbms_sql.execute" statement in the compile, it choked. I'm at
home and I don't have the exact message with me, but it was something

to the effect of not recognizing the ".execute".

Thanks again for your help.

"Jim Kennedy" <kennedy-family_at_home.com> wrote in message news:<3TVj7.444149$p33.8508662_at_news1.sttls1.wa.home.com>...
> 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 Sun Sep 02 2001 - 10:12:28 CDT