Dynamically call PL/SQL procedure with output variables

Question

I'm trying to use dynamic sql to execute a procedure and retrieve the output parameters:

v_sql := 'BEGIN ' || p_procname || '(''' || p_input1 || ''', ''' || p_input2 || ''', v_output1); END;';
DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql;

I have the v_output1 variable declared, but I get this error message:

PLS-00201: identifier 'V_ISSUE' must be declared 

The procedure works if I replace the above code with the dbms_ouput from v_sql, so it's OK on the procedure side, the issue is somewhere in how I'm trying to call it dynamically.

I don't know if what I'm trying to do will work this way. Is there a way to retrieve the output parameters from this procedure call through dynamic SQL?

Answer 1

Don't use string concatenation to pass parameters to dynamic SQL. It's considered a bad practice at all, not only for Oracle.

It's a bad, but main point of failure is using name of local variable inside dynamic SQL string, because it not visible anywhere outside code block where this variable was introduced.

Your code should look like this:

declare 
  v_sql varchar2(4000);
  p_procname varchar2(100);
  p_input1 number;
  p_input2 number;
  v_output1 number;   
begin

  v_sql := 'begin ' || p_procname || '(:p_input1, :p_input2, :v_output); end;';

  execute immediate v_sql 
  using in p_input1, in p_input2, out v_output1;

end;