Parameterized sql query in PL/SQL block - Bind Variables

Beware of parameterized queries. Parameters are sequential and the number changes when you add any additional parameter for new requirement, subsequently you will need to change the later used bind variables accordingly.

Moreover it does not matter what number you use after colon (:) , it will simple consider it next parameter.

In PL/SQL query does not recognize bind variable number used, instead it will accept based on position

e.g

set serveroutput on size 10000;

declare

l_x number := 1;

l_y number := 2;

l_z number := 3;

l_op1 varchar2(15) :='No';

l_op2 varchar2(15) :='No';

l_op3 varchar2(15) :='No';

begin

execute immediate '

select sysdate from dual

where 1 =:1

and 2 =:3

and 3 = :2'

into l_op1

using l_x , l_y, l_z;

dbms_output.put_line (' Output 1 : '||l_op1);

execute immediate '

select sysdate from dual

where 1 =:1

and 2 =:35

and 3 = :2'

into l_op2

using l_x , l_y, l_z;

dbms_output.put_line (' Output 2 : '||l_op2);

execute immediate '

select sysdate from dual

where 1 =:1

and 2 =:3

and 3 = :2'

into l_op3

using l_x ,  l_z,l_y;

dbms_output.put_line (' Output 3 : '||l_op3);

exception when no_data_found then 

dbms_output.put_line (' ERROR : '||SQLERRM);

end;

Results

 Output 1 : 22-SEP-20

 Output 2 : 22-SEP-20

 ERROR : ORA-01403: no data found

PL/SQL procedure successfully completed.

Last select in the block fails to identify l_y is at position 3 .

If you use simple sql query , this will work but not in PL/SQL

select sysdate from dual

where 1 =:1

and 2 =:3

and 3 = :2;

Input values 

:1 = 1

:2 = 3

:3 = 2

Results

22-SEP-20