Possible for Oracle PL/SQL to use dynamic procedure name?

Question

I am trying to pass a loop the name of the procedure I want to call as I need to call 3 similar procedures in a row.

Lets call them:

1. Proc_A
2. Proc_B
3. Proc_C

They each have 1 input variable and 2 output variables.

Any ideas on what I can change to get this to work?

FOR l_counter in 1..3
LOOP
    SELECT PROC_NAME into V_PROC FROM PROC_LIST WHERE PROC_ID = l_counter;

    EXECUTE IMMEDIATE 'PROC_DB.' || V_PROC || '(1,V_STEP_ERROR_CODE,V_STEP_MSG)';
    COMMIT;
END LOOP;

Line 5 currently fails. (The Execute Immediate) with: "invalid SQL statement"

Answer 1

You "forgot" to include BEGIN-END. You're calling a procedure, and that's PL/SQL.

For example: my table contains several procedures, all of them look the same - they just display their name.

SQL> select * from proc_list;

   PROC_ID PR
---------- --
         1 p1
         2 p2
         3 p3

SQL> create procedure p1 as begin dbms_output.put_Line('p1'); end;
  2  /

Procedure created.

Here's what you should have done (note line #8):

SQL> declare
  2    v_proc proc_list.proc_name%type;
  3  begin
  4    for l_counter in 1 .. 3 loop
  5      select proc_name into v_proc from proc_List
  6      where proc_id = l_counter;
  7
  8      execute immediate 'begin ' || v_proc ||'; end;';
  9    end loop;
 10  end;
 11  /
p1
p2
p3

PL/SQL procedure successfully completed.