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