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