how do you call procedures dynamically
From: Simon Spencer <Simon.Spencer_at_citicorp.com>
Date: 1997/02/11
Message-ID: <3300C26E.54A4_at_citicorp.com>#1/1
-- have tried this both with an without the bind variable. Someone
suggested that a select
-- procedurename from dual would work, but not for me.
Simon Spencer
06902 USA
Received on Tue Feb 11 1997 - 00:00:00 CET
Date: 1997/02/11
Message-ID: <3300C26E.54A4_at_citicorp.com>#1/1
[Quoted] [Quoted] I am looking for a way to call a procedure dynamically (ie, not knowing the name of the procedure at compile time). I have tried quite a few methods but without any luck, I believe that the DBMS_SQL package should allow me to achieve my goal, but I am stuffed if i know how to get it to work.
My first attempt went along the lines of
procedure callIt is
myVariable varchar2(1000) := 'callThisProcedure';
begin
myVariable; -- a vein attempt to run the procedure
"callThisProcedure"
end;
Below is my latest attempt to get this thing working. Any suggestions?
procedure callIt is
handle1 integer; alteredRows integer; begin handle1 := DBMS_SQL.OPEN_CURSOR;
-- have tried this both with an without the bind variable. Someone
suggested that a select
-- procedurename from dual would work, but not for me.
DBMS_SQL.PARSE (handle1, 'select :proc from dual', DBMS_SQL.V7); DBMS_SQL.BIND_VARIABLE(handle1, ':proc', 'theProcedureIWantToCall); alteredRows := DBMS_SQL.EXECUTE(handle1); DBMS_SQL.CLOSE_CURSOR(handle1); exception when others then DBMS_SQL.CLOSE_CURSOR(handle1);end;
Simon Spencer
Vice President Work: 203-961-6140 IPD, Operations and Technology Fax: 203-961-6163 Emerging Technologies Group Cell: 100 First Stamford Place Email: simon.spencer_at_citicorp.com7th Floor, Stamford CT.
06902 USA
Received on Tue Feb 11 1997 - 00:00:00 CET