Oracle JDBC batchUpdate rows affected is always -2 (Statement.SUCCESS_NO_INFO)

Question

I have a batch process which does Bulk UPDATE statement.

After implementing batch support using Spring JDBC 4.1.6 and Oracle Jdbc Driver (ojdbc7 and ucp), the number of records affected for individual update requests (in the batch) are always retrieved as -2 (Statement.SUCCESS_NO_INFO).

Is there any way to know the rows affected for individual update requests (argument set in batch), as I have to retry the same argument with an INSERT statement after this ??

Technically trying to develop this as an UPSERT implementation

I tried this batch update in Three Different Ways, and in all three methods the results are same -- (It just tells me Statement.SUCCESS_NO_INFO (-2) )

Method 1 -- Direct UCP Connection and PreparedStatement

    connectionPoolMgr.startConnectionPool("mgr_pool");
    Connection connection = pds.getConnection();

    PreparedStatement pstmt = connection.prepareStatement(dmlSQL);
    pstmt.setInt(1, pkId);
    pstmt.setInt(2, idx * 10);
    pstmt.addBatch();

   // EVERY ELEMENT IN THIS ARRAY IS ALWAYS returned as -2
   int updatedRows[] = pstmt.executeBatch();

Method 2 - Spring JdbcTemplate and batchUpdate()

 MapSqlParameterSource[] paramsArray = getSqlParameterList().toArray(new MapSqlParameterSource[0]);

      // EVERY ELEMENT IN THIS ARRAY IS ALWAYS returned as -2
 int[] batchUpdateResult = getNamedParameterJdbcTemplate().batchUpdate(sqlStatement, paramsArray);

Method 3 - Spring BatchSqlUpdate implementation

   BatchInsert batchInsert = new BatchInsert(dataSource);
   for (int i = 0; i < count; i++) {
        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue("ID", i + 100);
        param.addValue("FIRST_NAME", "Name" + i);

        batchInsert.updateByNamedParam(param.getValues());
    }

    batchInsert.flush();
    int rowsAffected[] = batchInsert.getRowsAffected();

    class BatchInsert extends BatchSqlUpdate  {
          private static final String SQL = "UPDATE t_customer_test SET first_name = :FIRST_NAME)  WHERE id  = :ID";

          BatchInsert(DataSource dataSource) {
              super(dataSource, SQL);
              declareParameter(new SqlParameter(Types.VARCHAR));
              declareParameter(new SqlParameter(Types.INTEGER));
              setBatchSize(100);
              compile();
         }
  }

Asked the same question on OTN network, and got some useful answers. Currently I am trying to implement this as UPSERT using MERGE Statement. Link: community.oracle.com/message/12980203 — Rajesh Balan

Jean de Lavarene Jean de Lavarene · Accepted Answer · 2015-05-06T21:55:36

Starting in 12.1, the Oracle Database returns the number of updated rows for each element of the batch. You will need a 12.1 Database and Driver (12.1.0.2). This feature doesn't exist in earlier versions of the Database.

So starting in 12.1:

int updatedRows[] = pstmt.executeBatch();

will actually return an array containing the number of updated rows for each element in the batch instead of -2. BTW this feature is only in the JDBC thin driver.

Oracle JDBC batchUpdate rows affected is always -2 (Statement.SUCCESS_NO_INFO)

1 Answers