Re: Oracle JDBC error

On Jul 26, 12:39 am, Peter Ashford <peter.m.ashf...@xxxxxxxxx> wrote:
I'm getting this error doing an SQL insert:

java.sql.SQLException: ORA-01745: invalid host/bind variable name

I know there's no invalid names in the insert because I can insert
data into the table by hand over sqlplus.

I'm using oracle's ojbdc14 jdbc driver and Java 1.6.

Any ideas?

Peter,

You (or a library you are using) are very likely passing invalid,
un-parsable SQL to Oracle. Are you using JDBC's
Connection.prepareStatement()? If so, post the SQL INSERT
statement you are supplying.

Oracle, when accessed via JDBC and similar libraries, processes the
SQL statement in two phases: (1) preparing, where the SQL is
parsed/compiled and (2) execution, where the SQL is actually run,
including the substuting of actual values for "bind variable"
placeholders.

You are not getting past step one (1) as evidenced by ORA-01745
which is a parse time error. The use of a reserved word or
invalid characters in a "colon variable" will trigger this.
E.g.:

SELECT :view FROM dual

or

SELECT :&^ FROM dual

Your test in SQL*Plus (probably?) did not use bind variables and
so is not meaningful for diagnosing the problem.

Pls. post that SQL!

John Hinsdale

PS: JDBC could be more helpful here. I know for a fact that
Oracle's libraries provide the location within the text of the
SQL statement where parse problems occur, and JDBC could use this
to tell you exactly where the illegal variable name was
encountered. For example, the Oracle interface to the Lisp
language (i.e., my library for CLisp), when given an invalid bind
variable name, will steer you to the exact location of the error:

[5]> (oracle:run-sql "SELECT :valid_name, :view FROM dual")

*** - Error executing SQL query:
---
SELECT :valid_name, :view FROM dual
---
ORA-01745: invalid host/bind variable name
At character 21, near ==> indicator in:
----
SELECT :valid_name, :
==> view FROM dual
----

Bunce's perl library, DBD::Oracle also echoes back the query with
the exact location of the parse error in a similar fashion. So
you should be aware that w/ JDBC is not giving you all the
helpful information Oracle has to offer about the context of the
parse error.

.

Relevant Pages