How to get the generated key for a column with lowercase characters from Oracle using JdbcTemplate (or plain JDBC)

Question

If I create a table like this:

create table standard (
    id NUMBER GENERATED by default on null as IDENTITY PRIMARY KEY, 
    text varchar2(200)
)

I can insert some data and get the generated id back with a method like this:

Object insertAndReturnStandardId() {

    GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
    template.update("insert into standard (text) values('test text')",new MapSqlParameterSource(), keyHolder, new String[]{"id"});

    return  keyHolder.getKey();
}

template here is a NamedParameterJdbcTemplate, but since there are no parameters it should be equivalent to a similar call to JdbcTemplate.

But if I use a slightly different table with a lower case column name for the id:

create table lowercase (
    "id" NUMBER GENERATED by default on null as IDENTITY PRIMARY KEY, 
    text varchar2(200)
)

Passing the column name id fails:

Object insertAndReturnLowerCaseId() {
    GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
    template.update("insert into lowercase (text) values('test text')",new MapSqlParameterSource(), keyHolder, new String[]{"id"});

    return  keyHolder.getKey();
}

With (full call stack below)

PreparedStatementCallback; bad SQL grammar [insert into lowercase (text) values('test text')]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "ID": invalid identifier

Passing the column name quoted "id" fails as well:

private Object insertAndReturnLowerCaseId() {
    GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
    template.update("insert into lowercase (text) values('test text')",new MapSqlParameterSource(), keyHolder, new String[]{"\"id\""});

    return  keyHolder.getKey();
}

With (full call stack below)

PreparedStatementCallback; uncategorized SQLException for SQL [insert into lowercase (text) values('test text')]; SQL state [99999]; error code [17068]; Invalid argument(s) in call; nested exception is java.sql.SQLException: Invalid argument(s) in call

So: How do I get the generated value from an Oracle database when the id column is lower case? A solution based on the NamedParameterJdbcTemplate or the JdbcTemplate are preferred, but I take an answer based on plain JDBC just as well.

Full call stack when id IS NOT quoted in call to update

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into lowercase (text) values('test text')]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00904: "ID": invalid identifier

    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:893) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:349) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at de.schauderhaft.dbtestclient.DbtestclientApplication.insertAndReturnLowerCaseId(DbtestclientApplication.java:61) ~[classes/:na]
    at de.schauderhaft.dbtestclient.DbtestclientApplication.run(DbtestclientApplication.java:45) ~[classes/:na]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:795) ~[spring-boot-2.3.0.RELEASE.jar:2.3.0.RELEASE]
    ... 5 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "ID": invalid identifier

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:970) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3756) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3736) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1063) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at org.springframework.jdbc.core.JdbcTemplate.lambda$update$1(JdbcTemplate.java:894) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    ... 10 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00904: "ID": invalid identifier

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    ... 26 common frames omitted

Full call stack when id IS quoted in call to update

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [insert into lowercase (text) values('test text')]; SQL state [99999]; error code [17068]; Invalid argument(s) in call; nested exception is java.sql.SQLException: Invalid argument(s) in call
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:893) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:349) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at de.schauderhaft.dbtestclient.DbtestclientApplication.insertAndReturnLowerCaseId(DbtestclientApplication.java:61) ~[classes/:na]
    at de.schauderhaft.dbtestclient.DbtestclientApplication.run(DbtestclientApplication.java:45) ~[classes/:na]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:795) ~[spring-boot-2.3.0.RELEASE.jar:2.3.0.RELEASE]
    ... 5 common frames omitted
Caused by: java.sql.SQLException: Invalid argument(s) in call
    at oracle.jdbc.driver.AutoKeyInfo.getNewSql(AutoKeyInfo.java:189) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:4656) ~[ojdbc8-19.3.0.0.jar:19.3.0.0.0]
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:230) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
    ... 10 common frames omitted

Not something I use, but from the docs, does [`setResultsMapCaseInsensitive()`](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html#setResultsMapCaseInsensitive-boolean-) affect what you see? (If it does and is set to true, your original query will then fail, presumably...) — Alex Poole, Jun 08 '20 at 13:57

score 2 · Accepted Answer · answered Jun 08 '20 at 13:56

2

There's a JDBC driver bug SR 3-18090632291. Some info an be found here:

This is really difficult to work around, because the JDBC driver tampers with the SQL string, and you cannot really do anything about that.

answered Jun 08 '20 at 13:56

Lukas Eder

211,314
129
689
1,509

2

Isn't it a joy that each JDBC driver messes around with the actual SQL? – mp911de Jun 08 '20 at 14:17
1

@mp911de: This is "just" a bug... The intention is reasonable. Behind the scenes, some PL/SQL is generated to be able to fetch generated values. Most JDBC users probably don't want to do that manually... – Lukas Eder Jun 08 '20 at 14:36

How to get the generated key for a column with lowercase characters from Oracle using JdbcTemplate (or plain JDBC)

Full call stack when id IS NOT quoted in call to update

Full call stack when id IS quoted in call to update

1 Answers1