Re: Oracle JDBC error

On Jul 26, 12:39 am, Peter Ashford <peter.m.ashf..._at_gmail.com> 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. Received on Thu Jul 26 2007 - 10:15:38 CDT