Thursday, April 10, 2008

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

4 comments:

Patrick Wolf said...

Hi Flavio,

I don't really see a difference between your first example and the second one.

1) execute immediate 'begin dbms_application_info.set_module(:1); end;' using a, b;
2) execute immediate 'begin dbms_application_info.set_module(:1); end;' using a, b;

Maybe it's a copy/paste error.
Patrick

Byte64 said...

you're right Patrick,
i've now added a :2 in the second PL/SQL snippet to make it work!

Thank you!

Flavio

alvalongo said...

Thanks, until now I cann't understand the ORA-01006 error.
You save a lot.
bye
Alvaro
from Colombia (South America)

alvalongo said...

Thank you
Until now I cann't understand the "ORA-01006" error and how to fixed.
bye
Alvaro
From Colombia (South America)

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles