plsql - How to call a stored procedure dynamically? -

for rec in ( select procedure_name                   datamart_process_steps                  order procedure_order ) loop   execute rec; end loop; 

i have procedure manages series of procedures table datamart_process_steps, , need run every procedure within table dynamically. oracle sql developer not way executing procedures; throws syntax error. proper way achieve task?

execute 'begin ' || rec || '; end'; 

i have tried after reading through tutorial on stored procedures, has issue single quotes. help. if need more detail or code please ask.

thank in advance.

the execute command sql*plus/sql developer shorthand anonymous pl/sql block. isn't valid inside other pl/sql, including inside master procedure.

dynamic calls within pl/sql use unrelated execute immediate statement, , syntax diagram shows immediate keyword not optional.

the dynamic sql statement in case needs anonymous pl/sql block around cursor-supplied procedure name, you've realised. second version missing semicolon after end you're concatenating onto string; , you're referring rec directly rather column within record type, i.e. using name of column cursor selecting.

so loop needs do:

for rec in ( select procedure_name                   datamart_process_steps                  order procedure_order ) loop   execute immediate 'begin ' || rec.procedure_name || '; end;'; end loop;