using EXECUTE IMMEDIATE with multiple same bind arguments

oracle, plsql

Solution

There is "special" behaviour in Oracle: Repeated Placeholder Names in Dynamic SQL Statements

In an Anonymous Block or CALL Statement it is not required to repeat the bind values if the names are equal. For example this Anonymous Block is working:

DECLARE
  a NUMBER := 4;
  b NUMBER := 7;
  plsql_block VARCHAR2(100);
BEGIN
  plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
  EXECUTE IMMEDIATE plsql_block USING a, b;  -- calc_stats(a, a, b, a)
END;
/

But this `EXECUTE IMMEDIATE plsql_block USING a, b;` does not work inside a Procedure.

Problem

When I create the following procedure ``` create or replace procedure check_exec_imm( tab IN VARCHAR2, col IN VARCHAR2, col_name IN VARCHAR2 ) IS cv SYS_REFCURSOR; col_value VARCHAR2(32767); lv_query VARCHAR2(32767); BEGIN lv_query := 'SELECT ' ||col|| ' FROM ' ||tab|| ' WHERE (:1 = ''EUR'' OR :1 = ''USD'') and rownum <=1'; EXECUTE IMMEDIATE lv_query INTO col_value USING col_name ; DBMS_OUTPUT.PUT_LINE('COLUMN VALUE : ' || col_value); END; ``` When the procedure is executed, I'm getting the following error ``` ORA-01008: not all variables bound ORA-06512: at "GRM_IV.CHECK_EXEC_IMM", line 18 ORA-06512: at line 2 ``` When I give the bind argument col_name again as below, the procedure is running fine. ``` EXECUTE IMMEDIATE lv_query INTO col_value USING col_name, col_name ; ``` Why oracle is behaving differently in this procedure. Since, it is the same bind variable, one bind argument should be sufficient right..!!? Please explain where I'm getting my logic wrong.

Original source