java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended for INSERT INTO SELECT

Question

I have a below query, which needs to select a row by using a column as key and return generated keys.

INSERT INTO t_tpms_cc_request
            (process_identifier,
             request_source_id,
             amount,
             etc_account_id,
             retry_count,
             status,
             store_identifier,
             version_no,
             next_process_time,
             composite_transaction_id,
             payment_id,
             processed_time,
             replenishment_id,
             pay_type,
             agency_id,
             response_code,
             file_id,
             request_date,
             auth_file_id,
             auth_date_time,
             merc_file_id,
             merc_date_time,
             cc_num,
             cc_expiration_date,
             merchant_id,
             ext_sys_ref,
             encrypt_cc_number,
             cc_month_cd,
             cc_year_cd,
             orig_txn_ref,
             auth_code,
             avs_code,
             cvv_code)
SELECT CC.process_identifier,
       CC.request_source_id,
       CC.amount,
       CC.etc_account_id,
       CC.retry_count,
       CC.status,
       CC.store_identifier,
       CC.version_no,
       CC.next_process_time,
       CC.composite_transaction_id,
       CC.payment_id,
       CC.processed_time,
       CC.replenishment_id,
       CC.pay_type,
       CC.agency_id,
       CC.response_code,
       CC.file_id,
       CC.request_date,
       CC.auth_file_id,
       CC.auth_date_time,
       CC.merc_file_id,
       CC.merc_date_time,
       CC.cc_num,
       CC.cc_expiration_date,
       CC.merchant_id,
       CC.ext_sys_ref,
       CC.encrypt_cc_number,
       CC.cc_month_cd,
       CC.cc_year_cd,
       CC.orig_txn_ref,
       CC.auth_code,
       CC.avs_code,
       CC.cvv_code
FROM   t_tpms_cc_request CC
WHERE  CC.order_id = ?

And, I have wrriten a below java code to do this:

String key[] = {"order_id"}; 

DataSource ds = null;  
Connection con = null;
ResultSet rs = null;
try {
      ds = jdbcTemplate.getDataSource();
      con = ds.getConnection();
      PreparedStatement ps = 
          con.prepareStatement(insertCCRequest.trim(), key);
    ps.setString(1, OrderId);
      int i= ps.executeUpdate();
      rs = ps.getGeneratedKeys();
      if (rs.next()) {
         return rs.getString(1);
      }
} catch (SQLException e) {
    logger.debug("SQL exception in RebillDao.insertCCrequest() 
                                       method..!! ");
    logger.debug("Exception cause: "+e.getMessage());
    e.printStackTrace();
    throw e;
}
finally {
      if(con!=null){
            con.close();
      }          
}
return "";

When i run this, I get below exception:

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

Please tell me the ways to fix this.

Also, Using JDk 1.6 and ojdbc6-11.2.0.4.jar

Did you try to use an upper case column name? String key[] = {"ORDER_ID"}; — user330315
– user330315, Commented Jan 24, 2017 at 7:01

Luke Woodward · Accepted Answer · 2017-01-22 20:58:18Z

2

I suspect that when you use generated keys with a prepared statement, the Oracle JDBC driver adds the RETURNING INTO clause to the INSERT statement, and that the JDBC driver is too dim to realise that the RETURNING INTO clause can't be used with INSERT INTO ... SELECT ... statements. I get the same ORA-00933 error if I attempt to run an INSERT INTO ... SELECT ... RETURNING ... statement.

What you could try instead is a PL/SQL block where we fetch the 'old' row into a record and then use an INSERT ... VALUES statement with a RETURNING_INTO clause to insert the values into the 'new' row:

DECLARE
  l_row     t_tpms_cc_request%ROWTYPE;
BEGIN
  SELECT * INTO l_row FROM t_tpms_cc_request WHERE order_id = ?;
  INSERT INTO t_tpms_cc_request (some_column, some_other_column, ...)
    VALUES (l_row.some_column, l_row.some_other_column, ...)
  RETURNING order_id INTO ?;
END;

As we're returning values from this, we need to prepare this as a CallableStatement instead of a PreparedStatement, and we need to register parameter 2 as an out parameter. We can then use this out parameter, instead of the getGeneratedKeys() method you're using at the moment, to return the generated key value.

Clearly this approach is Oracle-specific and won't work on other databases. I don't know how much of an issue database portability is to you, nor whether you can return generated keys from an INSERT INTO ... SELECT ... statement in other databases.

answered Jan 22, 2017 at 20:58

Luke Woodward

65.4k16 gold badges94 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

GACy20 Over a year ago

What a bummer. I ended up splitting the insert into select into 2 queries, I first fetch the data and then use a simple insert into ... values. Obviously I pay the roundtrip oracle->java->oracle etc but I find it easier to look at then PL/SQL especially until we do not have performance issues. A real shame that 4 years later we still have this problem with the driver.

Collectives™ on Stack Overflow

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended for INSERT INTO SELECT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related