Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-00904 invalid identifier when trying to insert into a table with an generated key using a quoted identifier

Lukas EderOct 29 2024 — edited Oct 29 2024

Using ojdbc11 version 23.5.0.24.07 and database version 23.4.0.24.05 (Oracle 23ai free), create this table using quoted, lower case identifiers:

CREATE TABLE "t_17506" ("id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, "val" INT)

And then run this program in Java:

try (PreparedStatement s = connection.prepareStatement(
   """
   insert into "t_17506" ("val") values (?)
   """,
   new String[] { "id" }
)) {
   s.setInt(1, 1);
   s.executeUpdate();
   try (ResultSet rs = s.getGeneratedKeys()) {
       System.out.println(rs.getMetaData().getColumnCount());
       while (rs.next()) {
           for (int i = 0; i < rs.getMetaData().getColumnCount(); i++)
               System.out.println(rs.getMetaData().getColumnName(i + 1) + rs.getString(i + 1));
       }
   }
}

To get this exception stack trace:

https://docs.oracle.com/error-help/db/ora-00904/
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:709)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:609)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1347)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1100)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:408)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:499)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:274)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1484)
    at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:2008)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1621)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3955)
    at oracle.jdbc.driver.OraclePreparedStatement.doExecuteLargeUpdate(OraclePreparedStatement.java:4314)
    at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:4291)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4273)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:997)
    at org.jooq.testscripts.JDBC.main(JDBC.java:42)
Caused by: Error : 904, Position : 54, SQL = insert into "t_17506" ("val") values (:1 )
RETURNING id INTO :2  [SQL INCLUDES EXPRESSIONS ADDED BY THE ORACLE JDBC DRIVER], Original SQL = insert into "t_17506" ("val") values (?)
RETURNING id INTO ?, Error Message = ORA-00904: "ID": invalid identifier
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:717)
    ... 15 more

It appears that the JDBC driver fails to add the quotes around the "id" identifier.

A workaround seems to be to wrap the identifier in quotes when calling JDBC:

try (PreparedStatement s = connection.prepareStatement(
   """
   insert into "t_17506" ("val") values (?)
   """,
   new String[] { "\"id\"" }
)) { ... }

But just like in this case here, I feel this shouldn't be necessary, because it's hardly ever necessary elsewhere and it feels like this logic should be handled by the JDBC driver. A similar case where this needs to be done (but shouldn't be needed) is:

https://forums.oracle.com/ords/apexds/post/ora-17068-invalid-arguments-in-call-when-looking-up-quoted-4512

Comments

Add EVENT_10842=15

You can also set this as an environment variable with the same name & value.

Sheeraz Majeed May 10 2024 — edited on May 10 2024

Hi, Thanks for reply.

But still no trace or log files to check used oci functions

Works for me.

cjones@cjones-mac:~$ rm -rf $HOME/instantclient/log/diag/clients && mkdir -p $HOME/instantclient/log/diag/clients
cjones@cjones-mac:~$ export EVENT_10842=15
cjones@cjones-mac:~$ sqlplus cj/cj@localhost/orclpdb1

...

SQL> select * from dual;

D
-
X

SQL> exit

And then a file like /Users/cjones/instantclient/log/diag/clients/user_cjones/host_nnnnnn_nn/trace/ora_nnn_nnnnn.trc will contain lines like:

# 2024-06-06 07:26:54.520 # Thread ID 8547126272 # Entry - OCIServerAttach(srvhp = 0x128030068, errhp = 0x13580a410, dblink_hash = H:0x9bf0f8e9ee161fdf, mode = OCI_DEFAULT(000000000), dblink = localhost/orclpdb1, dblink_len = 18 );
1 - 3

Post Details

Added on Oct 29 2024
0 comments
448 views