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.

`When I give the bind argument col_name again` Yes, you have to provide the same variable twice. Besides identifiers, e.g. column names or table names, cannot be bound, only literals. So this `WHERE (:1 = ''EUR'' OR :1 = ''USD'')` seems not quite correct - instead of identifier a character literal will be used. — Nick Krasnov, Feb 20 '14 at 05:48
@ Nicholas I've written this procedure to check with multiple same bind arguments. Because, in another procedure while I used another same bind variable multiple times, the `EXECUTE IMMEDIATE` was working fine. is there a way that it can be done. I couldn't find out the difference. — ethan, Feb 20 '14 at 05:59
Your logic is correct but it's just not the way it works in Oracle. — Rene, Feb 20 '14 at 06:43

score 13 · Accepted Answer · edited Sep 19 '18 at 09:30

13

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.

edited Sep 19 '18 at 09:30

Vadzim

24,954
11
143
151

answered Feb 20 '14 at 06:57

Wernfried Domscheit

54,457
9
76
110

Is there a way to make it work? Because I am executing multiple queries in a loop and each query has multiple same bindings. – Mani Sankar Feb 23 '17 at 07:27
@ManiSankar what do you mean? Please open a question with your problem. – Wernfried Domscheit Feb 23 '17 at 08:44

score 2 · Answer 2 · answered Feb 20 '14 at 05:59

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.

using EXECUTE IMMEDIATE with multiple same bind arguments

3 Answers3

Linked