Getting inserted ID after INSERT ... SELECT on Oracle

Question

This SQL statement works if I run it on my Oracle client (SQL Developer):

insert into Person (Name) select 'Bob' from dual 

It also works if I issue it through Spring JDBC without using KeyHolder :

 final PreparedStatementCreator psc = new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { return con.prepareStatement( "insert into Person (Name) select 'Bob' from dual"); } }; jdbcOperations.update(psc); 

However, I need to use KeyHolder to get the id of the newly inserted row. If I change the above code, use KeyHolder as follows:

 final KeyHolder keyHolder = new GeneratedKeyHolder(); final PreparedStatementCreator psc = new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { return con.prepareStatement( "insert into Person (Name) select 'Bob' from dual", new String[] {"PersonID"}); } }; jdbcOperations.update(psc, keyHolder); 

... then I get this error:

 Exception in thread "main" org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:94) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:842) at au.com.bisinfo.codecombo.logic.ImportServiceImpl.insertLoginRedirectRule(ImportServiceImpl.java:107) at au.com.bisinfo.codecombo.logic.ImportServiceImpl.runImport(ImportServiceImpl.java:68) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) at $Proxy8.runImport(Unknown Source) at au.com.bisinfo.codecombo.ui.Main.main(Main.java:39) Caused by: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205) at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449) at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3530) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:844) at org.springframework.jdbc.core.JdbcTemplate$3.doInPreparedStatement(JdbcTemplate.java:1) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586) ... 15 more 

FWIW, everything is fine if I do INSERT ... VALUES instead of INSERT ... SELECT (although this does not help me since I need to choose things):

 final KeyHolder keyHolder = new GeneratedKeyHolder(); final PreparedStatementCreator psc = new PreparedStatementCreator() { @Override public PreparedStatement createPreparedStatement(Connection con) throws SQLException { return con.prepareStatement( "insert into Person (Name) values ('Bob')", new String[] {"PersonID"}); } }; jdbcOperations.update(psc, keyHolder); 

I use:

• Spring JDBC 3.0.3.RELEASE
• JDBC driver: ojdbc6.jar version 11.2.0.1.0
• RDBMS: Oracle9i Release 9.2.0.5.0 - Production
• commons-dbcp 1.4

NB my application needs to use standard SQL to remain neutral db, which excludes any Oracle-specific SQL code (I will not choose from a "double" in real life).

Thanks for any help.

Answer 1

java.sql.Connection.prepareStatement (java.lang.String, int) interface is understandable

Creates a default PreparedStatement object that has the ability to retrieve automatically generated keys

So you are using the wrong method. Try

 return con.prepareStatement( "insert into Person (Name) select 'Bob' from dual", Statement.RETURN_GENERATED_KEYS); 

instead

Answer 2

This feature is not supported by the Oracle JDBC driver.

Answer 3

What about

 INSERT INTO blah b (blah1, blah2, blah3) VALUES (?, ?, ?) RETURNING b.id INTO ?";