Calling PLSQL stored procedure using string representation of name

Question

I have a list of procedures to be called in a given order for all rows in a table. Rather than hardcoding the procedure calls, I would like to add them all to a separate table and loop over the rows containing the procedures calling each one. All procedures requires the same input parameters.

Is this at all possible?

Answer 1

Say you have this table:

create table procs(procName) as (
    select 'proc1' from dual union all
    select 'proc2' from dual union all
    select 'proc3' from dual 
)    

and these procedures:

create or replace procedure proc1(p1 in varchar2, p2 in number) is begin dbms_output.put_line('running Proc1(' || p1 || ', ' || p2 || ')'); end;
create or replace procedure proc2(p1 in varchar2, p2 in number) is begin dbms_output.put_line('running Proc2(' || p1 || ', ' || p2 || ')'); end;
create or replace procedure proc3(p1 in varchar2, p2 in number) is begin dbms_output.put_line('running Proc3(' || p1 || ', ' || p2 || ')'); end;

You may try:

declare
    yourParameter1 varchar2(10) := 'X';
    yourParameter2 number       := 10;
begin
    for i in ( select procName from procs order by procName) loop
        execute immediate 'begin ' || i.procName || '(:1, :2); end;' using yourParameter1, yourParameter2;
    end loop;
end;

What you get:

running Proc1(X, 10)
running Proc2(X, 10)
running Proc3(X, 10)