java.sql.SQLException: ORA-00933: SQL command not properly ended when using getGeneratedKeys

Question

When I am using the following query it works.

query = "INSERT INTO MLRL1_PSR_MASTER (PROJECT_ID,FROM_DATE,TO_DATE,TEMPLATE_ID,TEMPLATE_TYPE,UPLOADED_BY,PSR_SLABID) " +
        " select '"+projectId+"' , FROM_DATE , TO_DATE,'"+templateId+"','"+tempType+"','"+user.getUserID()+"', "+slabId+
        " from MLRL1_PSR_SLABS where SLAB_ID="+slabId+" ";

stmt = connection.prepareStatement(query, new String[] { "ID" });
stmt.executeUpdate();
stmt = connection.prepareStatement(query);

but if i use same query with getGeneratedKeys() like:

stmt = connection.prepareStatement(query, new String[] { "ID" });
stmt.executeUpdate();
ResultSet rs = stmt.getGeneratedKeys();

while (rs.next()) {
    masterId = rs.getInt(1);
}

I get an error

ORA-00933: SQL command not properly ended

stmt is java.sql.PreparedStatement, Code compliance is 1.6 and JRE is 1.7.67 Oracle Driver is odbc6 and database is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Answer 1

ResultSet rs = stmt.getGeneratedKeys();

the JDBC driver will add 'RETURNING . . . INTO . . ' to the end of the query you provide in order to return the values ask for. In your case SLAB_ID, which is always generated for INSERTs, was specified because you did not specify your own columns. When the 'RETURNING ..' is added to the end of your query the resulting syntax is invalid giving you the error message.

ORA-00933: SQL command not properly ended

RETURNING . . . is only supported for INSERT...VALUES; that is, for INSERT statements that use the VALUES clause to provide the values to be inserted. Inserts that use sub-queries do not support that syntax.

See the syntax diagram for the INSERT statement in the Oracle SQL reference https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm#SQLRF01604

Note that the 'returning_clause' is only shown on the 'values_clause' line and not on the 'subquery' line.

The syntax you are trying to use is not supported.

You can read more in:

http://database.developer-works.com/article/16369238/INSER+INTO+with+SELECT+is+throwing+error

https://community.oracle.com/thread/1325790