oracle - ExecuteBatch method return array of value -2 in java

Question

When I am executing executeBatch method in java, its returning an int array that's fine but the value is -2 of all array elements,

it should be 0 or +ve number that shows number of rows affected, but its returning -2,

when I checked it in database all updates done successful. please explain what is the meaning of this -2 and how I find number of rows affected.

Thank, Deepesh Uniyal

Answer 1

The jdbc-spec has the following to say about the return-code of batch-updates:

■ 0 or greater — the command was processed successfully and the value is an update count indicating the number of rows in the database that were affected by the command’s execution Chapter 14 Batch Updates 121

■ Statement.SUCCESS_NO_INFO — the command was processed successfully, but the number of rows affected is unknown

Statement.SUCCESS_NO_INFO is defined as being -2, so your result says everything worked fine, but you won't get information on the number of updated columns.

The oracle-documentation states:

? For a prepared statement batch, it is not possible to know the number of rows affected in the database by each individual statement in the batch. Therefore, all array elements have a value of -2. According to the JDBC 2.0 specification, a value of -2 indicates that the operation was successful but the number of rows affected is unknown.

? For a generic statement batch, the array contains the actual update counts indicating the number of rows affected by each operation. The actual update counts can be provided only in the case of generic statements in the Oracle implementation of standard batching.

? For a callable statement batch, the server always returns the value 1 as the update count, irrespective of the number rows affected by each operation.

So it seems if you need the update-counts you can't use PreparedStatements but have to fall back to plain Statements.