Re: Oracle JDBC error

On Jul 27, 3:15 am, "John K. Hinsdale" <h..._at_alma.com> wrote:
> 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.

Ok. Thanks for the very fullsome reply, by the way.

Here's the java code for the prepared statement:

  PreparedStatement pStmt = db.prepareStatement(

                "INSERT INTO departments ("+
                "   deptName, schoolName, division, dean, AVC, HOD,
admin, adminPhone, adminEmail," +
                "   streetAddress, city, cbCostCentre,
cbOHAccountCode, cbOHDisection, comments, " +
                "   schoolLiasonAdmin, dateEntered)" +
                "   VALUES (?,?,?,?,?,?,?,?,?,?"+
                "           ?,?,?,?,?,?,?)");

Note that I can do an insert like this by hand using SQLPLUS. I can also read / write other tables using the JDBC driver in the same application.

> 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.

No, I'd read the description of the ORA error, and it seemed like it didn't apply to me - this is a straight forward insert. There *is* a trigger to insert a unique id which uses a ":" variable in the database definition:

CREATE OR REPLACE TRIGGER trg_departments_insert

    BEFORE INSERT ON departments

        FOR EACH ROW
            BEGIN
                IF :new.id IS NULL THEN
                    SELECT seq_departments_ID.nextval INTO :new.id
FROM DUAL;
		END IF;
            END;

...but I thought that was pretty harmless. I use the same kind of code for all my tables' uids.

> Pls. post that SQL!

Done. Thanks again for all your feedback. Received on Thu Jul 26 2007 - 18:59:58 CDT