using EXECUTE IMMEDIATE with multiple same bind arguments

Question

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.

Answer 1

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.

Answer 2

The way you have referenced the column name through bind variable is not a preferred method as Nichoas pointed out. What you tried is called as native dynamic SQL using 'cleverer' bind variables.

In this method, you need to bind every parameter X times since you use it X times because they are all treated as separate variables.

Read more on binding to dynamic SQL.

Answer 3

@ethan and @ManiSankar I too had a same problem in my scenario as well. I solved this using some kind of brute force techinque. What i have done is

Before this EXECUTE IMMEDIATE lv_query INTO col_value USING col_name ;

I have added replace condition in my code by replacing parameter with the required value then called "Execute Immediate" without "using" clause

lv_query := replace(lv_query, ':1',col_name);
EXECUTE IMMEDIATE lv_query INTO col_value;

I don't know this is optimal one but served purpose for what i am expecting..

Please advice if this one recommended or not...