Loading...

Tuesday, April 08, 2008

SQL Error: ORA-01745: invalid host/bind variable name

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

This error can be returned in at least two distinct situations (and possibly more) involving native dynamic SQL.

The first situation occurs when you pick a reserved word for use as a bind variable name, for instance :USER or :SYDATE, just to name a couple (by the way, the letter casing is irrelevant).

Let's look instead at the following PL/SQL anonymous block for curious occurrence of this error:

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

SQL Error: ORA-01745: invalid host/bind variable name
There are a couple of things worth noting here:
first of all, the correct way of specifying a dynamic single-row select query is the following:

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

a:X

So, get rid of the INTO clause inside the SELECT and relocate it outside.
Secondly and funnily enough, if we write the original statement with :a instead of :1, Oracle doesn't complain at all:

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

a:

Even if oracle doesn't complain, this statement isn't of any practical use, because the bind variable :a is ignored altogether.
Note also that according to the documentation of 10R1 (it says the same thing in the docs of other versions) "you can place all bind arguments in the USING clause", but this isn't true for single-row SELECT queries (see ORA-01006) that require the special INTO clause shown above.

See message translations for ORA-01745 and search additional resources

3 comments:

Micah Burnett said...

Awesome. Thank you. I spent 3 hours trying to figure this out before I found your post.

Mark said...

Hey,

it seems like that the error occures too if you have too many query parameters defined e.g. a prepared statement in java that has more then ~64k parameters.

-Mark

Sean Ingram said...

Just wanted to say, thanks for a great page. You helped me crack an issue that's been bothering me all morning.

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