Restrictions on binding variables in dynamic SQL

Question

I came across a tricky situation this morning while I tried to bind variables in a dynamic SQL statement. The situation is that I am trying to use the same bind variable multiple times in a dynamic SQL block.

Consider the following code:

create or replace function test_function (v1 number, v2 number)
    return sys_refcursor
is
    cur sys_refcursor;
    v_sql clob := 'select 1 as col1,'
                ||chr(10)||'       nvl ( (select ''a = 2'''
                ||chr(10)||'                from dual'
                ||chr(10)||'               where :a = 2),' -- First occurance of `a`
                ||chr(10)||'            ''a != 2'')'
                ||chr(10)||'           as col2,'
                ||chr(10)||'       nvl ( (select ''a = 3'''
                ||chr(10)||'                from dual'
                ||chr(10)||'               where :a = 3),' -- Second occurance of `a`
                ||chr(10)||'            ''a != 3'')'
                ||chr(10)||'           as col2'
                ||chr(10)||'  from dual'
                ||chr(10)||' where :b = 1'; -- another var `b` used just once
begin
    open cur for v_sql using v1, v2;
    return cur;
end;
/

Then I run the following statement in Toad:

select test_function (3, 1) from dual;

I get the error:

ORA-01008: not all variables bound
ORA-06512: at "SCHEMA.TEST_FUNCTION", line 19

If I modify the dynamic SQL and remove the second occurrence of the bind variable a, it works. I tried to refer to Oracle docs here, here and here but found nothing related to this. Please help.

Answer 1

Variables in this case are bound by position, not by name. You have to repeat v1 variable like this

open cut for v_sql using v1, v1, v2;

See detailed description in http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#BHCHIHEJ