Question: (Logan Scott): I am
seeking an alternative to an Oracle procedure that calls other Oracle
procedures. I realize execute immediate is only for SQL. Is there a way to
do this alternative method? In a PL/SQL procedure I have this structure
located between a bunch of other commands, which calls other procedures:
if oappid = 1 then
--
tspace1.proc1 (1,2,3);
--
elsif oappid = 2 then
--
tspace2.proc1 (1,2,3);
--
elsif oappid = 3 then
--
tspace3.proc1 (1,2,3);
--
end if;
--
Each of these three procedures are located in other tablespaces (designated
tspace1,tspace2, and tspace3). Each time I add a new "application", I must then
add another elsif to this structure, for example, I add appid = 4 to run the
procedure in tablespace tspace4:
--
if oappid = 1 then
--
tspace1.proc1 (1,2,3);
--
elsif oappid = 2 then
--
tspace2.proc1 (1,2,3);
--
elsif oappid = 3 then
--
tspace3.proc1 (1,2,3);
--
elsif oappid = 4 then
--
tspace4.proc1 (1,2,3);
--
end if;
--
Instead of adding an elsif for every app, I would prefer to use something
similar to execute immediate, but instead of executing SQL, I want to execute a
procedure in which the command to execute the procedure is a string with a
variable used for the tablespace.
select tblspace into ospacename from applications where
appid = oappid;
oprocstring := ospacename || '.proc1 (1,2,3)';
execute immediate (oprocstring);
By using this method, I would not need to modify the master procedure every time
I add a new application. This method does not work, but do you know of any other
way this can be done?
I may have hundreds of applications, in which I would have to add hundreds of
elsifs to this if-endif structure, whereas my proposed alternative would only
require these three lines with execute immediate (or something comparable like
"execute procedure" that would work for executing other procedures from the
master procedure using a variable string:
select tblspace into ospacename from applications where appid = oappid;
oprocstring := ospacename || '.proc1 (1,2,3)';
execute procedure (oprocstring);
Answer: (Dr. Tim Hall): The EXECUTE
IMMEDIATE will process SQL or PL/SQL blocks, but what you have sent it is just a
procedure call, not a whole PL/SQL block. Going back to your first example:
ospacename := 'tspace2';
--
oprocstring := ospacename || '.proc1 (1,2,3)';
execute immediate (oprocstring);
This should be:
ospacename := 'tspace2';
--
oprocstring := 'BEGIN '|| ospacename || '.proc1 (1,2,3); END;';
execute immediate (oprocstring);
The addition of the BEGIN and END makes this a valid PL/SQL block.
You should really be using bind variables in the EXECUTE IMMEDIATE, so it would
be better to write:
ospacename := 'tspace2';
--
oprocstring := 'BEGIN '|| ospacename || '.proc1 (:b1,:b2,:b3); END;';
execute immediate (oprocstring) USING 1, 2, 3;
Notice the USING clause in the EXECUTE IMMEDIATE to bind the values to the bind
variables in the string.
Try this,
execute immediate ('begin '||oprocstring||'
end;');
In other words, wrap the string with a begin/end and it should work.
declare
vstring varchar2(10);
begin
vstring := 'testproc;';
execute immediate('begin '||vstring||' end;');
end;
/
Follow the link for more information on
Late Binding and Runtime Binding in PL/SQL.
 |
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts. |
and include the URL for the page.
