How to execute dynamic PL/SQL procedure calls

How to execute dynamic PL/SQL procedure calls

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, such as this:

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;
/