Java.sql.SQLException: ORA-00933: SQL command did not finish correctly when using getGeneratedKeys

Question

When I use 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 the 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 executed properly

stmt is java.sql.PreparedStatement , code matching is 1.6, and JRE is 1.7.67. The Oracle Driver is odbc6, and the 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 to return the values. In your case, the SLAB_ID, which is always generated for INSERT, was specified because you did not specify your own columns. When" RETURNING. . "is added to the end of your request, the resulting syntax is invalid, it gives an error message.

ORA-00933: SQL command not executed properly

RETURN., Supported only for INSERT ... VALUES; that is, for INSERT statements that use the VALUES clause to provide inserted values. Insertions that use subqueries do not support this syntax.

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

Please note that "return_clause" is displayed only in the line "values_clause", and not in the line "subquery".

The syntax you are trying to use is not supported.

You can learn more:

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

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

Answer 2

I think the syntax is wrong. You probably need to add “VALUES” to your query. The insert request should look like this: INSERT INTO TABLE_NAME VALUES ();