ORA-01006: bind variable does not exist

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Here is a common scenario for this error:
i specified two variables in the USING clause of the EXECUTE IMMEDIATE statement, however i didn't specify two bind variables in the PL/SQL string to be executed, there is only one variable (:1).

declare
a varchar2(50) := 'TEST PROCEDURE';
b varchar2(50) := 'DYNAMIC SQL';
c varchar2(50);
d varchar2(50);
begin
execute immediate 'begin dbms_application_info.set_module(:1); end;' using a, b;
dbms_application_info.read_module(c,d);
dbms_output.put_line(c);
dbms_output.put_line(d);
end;

ORA-01006: bind variable does not exist

After adding the bind variable as a second parameter for SET_MODULE, the program works correctly:

declare
a varchar2(50) := 'TEST PROCEDURE';
b varchar2(50) := 'DYNAMIC SQL';
c varchar2(50);
d varchar2(50);
begin
execute immediate 'begin dbms_application_info.set_module(:1,:2); end;' using a, b;
dbms_application_info.read_module(c,d);
dbms_output.put_line(c);
dbms_output.put_line(d);
end;

TEST PROCEDURE
DYNAMIC SQL

You can easily try out the code inside Apex SQL Workshop.

Another situation where you might get this error is when you attempt to execute this unsupported single-row native dynamic SQL statement:

declare
a varchar2(10);
b varchar2(10) := 'Y';
begin
execute immediate 'select dummy into :a from dual where dummy != :b' using out a, in b;
dbms_output.put_line(' a:'||a);
end;

ORA-01006: bind variable does not exist

however in this case the problem is with the syntax, not with the missing bind variable, because single-row dynamic SELECTs must be written with the special INTO clause as already explained in a previous posting (see ORA-01745).

See message translations for ORA-01006 and search additional resources