CODEWITHSUNDEEP
oracle-databasespringjdbcinsertora-00933
Andrew Swan
Andrew Swan

Reputation: 13627

Getting inserted ID after INSERT ... SELECT on Oracle

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

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

It also works if I issue it via Spring JDBC, without using a 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 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:

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'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):

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'm using:

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).

Thanks for any help.

Upvotes: 10

Views: 10023

Answers (4)

Dennis
Dennis

Reputation: 616

I suspect using a KeyHolder with an INSERT SELECT statement isn't and won't be supported because the select could theoretically select multiple rows, and if it did there would be no way to return those multiple keys into a single KeyHolder. For what you are trying to accomplish, it will likely be easier to simply use a select statement followed by an insert statement.

Upvotes: 1

bedrin
bedrin

Reputation: 4586

This feature isn't supported by Oracle JDBC Driver

Upvotes: 0

orbfish
orbfish

Reputation: 7731

How about

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

Upvotes: 0

Arthur Ronald
Arthur Ronald

Reputation: 33775

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

Creates a default PreparedStatement object that has the capability to retrieve auto-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

Upvotes: 1

Related Questions