title: Getting inserted ID after INSERT ... SELECT on Oracle
body: <p>This SQL statement works if I run it from my Oracle client (SQL Developer):</p>
  
  <pre><code>insert into Person (Name) select 'Bob' from dual
  </code></pre>
  
  <p>It also works if I issue it via Spring JDBC, without using a <a href="http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/jdbc/support/KeyHolder.html" rel="noreferrer">KeyHolder</a>:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>However I need to use a KeyHolder in order to get the ID of the newly inserted row. If I alter the above code to use a KeyHolder as follows:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>... then I get this error:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>FWIW, everything's fine if I do an INSERT ... VALUES instead of an INSERT ... SELECT (although this doesn't help me, as I need to select things):</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>I'm using:</p>
  
  <ul>
  <li>Spring JDBC 3.0.3.RELEASE</li>
  <li>JDBC driver: ojdbc6.jar version 11.2.0.1.0</li>
  <li>RDBMS: Oracle9i Release 9.2.0.5.0 - Production</li>
  <li>commons-dbcp 1.4</li>
  </ul>
  
  <p>N.B. my app needs to use standard SQL in order to remain db-neutral, which rules out any Oracle-specific SQL (I won't be selecting from "dual" in real life).</p>
  
  <p>Thanks for any help.</p>