0

Is it possible, if yes, what would the syntax look like for a stored proc which would pick a stored procedure from a list in a table and then executes it in an oracle db?

darlene levielle
  • 1
  • 3

1 Answers1

0

The stored procedure should use EXECUTE IMMEDIATE to execute an anonymous PL/SQL block with the procedure name. This can be as simple as creating a string like begin proc_name; end;. Things get more difficult if there are parameters and return values.

create table proc_table(id number, procedure_name varchar2(100));
insert into proc_table values(1, 'proc1');
insert into proc_table values(2, 'proc2');

create or replace procedure proc1 is begin dbms_output.put_line('1'); end;
/
create or replace procedure proc2 is begin dbms_output.put_line('2'); end;
/

begin
    for procedures in
    (
        select procedure_name
        from proc_table
        order by procedure_name
    ) loop
        execute immediate 'begin '||procedures.procedure_name||'; end;';
    end loop;
end;
/

Output:
1
2
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • I received this error PLS-00201: identifier 'IC_REP_BLUE_GOLD_SOC_THANK_YOU' must be declared when I replaced the 'proc1' in this part of code: insert into proc_table values(1, 'IC_Rep_Blue_Gold_Soc_Thank_You'); – darlene levielle Nov 18 '15 at 22:06
  • @darlenelevielle Does that procedure require any arguments? If so you may need to add some information to the table and to the execution code. Is it in the same schema as where the PL/SQL is run? If not then you may need to append the schema name in the table or in the code. – Jon Heller Nov 18 '15 at 22:12
  • I agree, I will add the schema name. That should do it. That proc doesn't not require any arguments, infact all the procs that will be inserted into this table will never require arguments. – darlene levielle Nov 19 '15 at 02:30