BatchUpdateException: the batch will not terminate

Question

I have an application which processes a very large file and sends data to an oracle database (using Java 6, oracle 9).

In a loop, I use a PreparedStatement ps and create all SQL statements generated with ps.addBatch().

I have a situation where a BatchUpdateException bue is thrown somewhere during the ps.executeBatch(). At that point, the batch stops to be executed.

I'd like the batch execution to continue, so that I can then check on failed updates in a method processUpdateCounts(bue.getUpdateCounts()).

The javadoc about class BatchUpdateException says:

After a command in a batch update fails to execute properly and a BatchUpdateException is thrown, the driver may or may not continue to process the remaining commands in the batch.

Is there a way to enforce continuation or do I need to alter my program so that it will execute the statement individually?

Answer 1

Just found this link: JDBC Batch Update Problem

Apparently, it says there there is

NO WAY WITH ORACLE BATCH JDBC to proceed after first failure,

thus I am resorting to sending the inserts one by one. Thank you

(sorry for not looking better to find the link above before).

Answer 2

there is a workaround that would allow you to use the batch feature. Instead of executing a simple INSERT statement, you can execute a PL/SQL block that will deal with the error appropriately:

BEGIN
   INSERT INTO your_table VALUES (?,?,...?);
EXCEPTION
   WHEN OTHERS THEN
      /* deal with the error. For example, log the error id and error msg 
         so that you can list them after the batch */
      INSERT INTO error_table VALUES (?, sqlerrm);
END

The performance should be on par with the batch insert (should be faster than individual execution of the statements). You could also call a stored procedure instead of a PL/SQL block.

Answer 3

Oracle itself can, see here: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci04sql.htm#sthref616

However, it doesn't seem that this functionality is exposed to JDBC, not even in the oracle specific classes.

Because of the rather useless JDBC error handling ("the driver may or may not continue"), I'm always setting a savepoint before the batch, and performing an rollback to that point on error. That's the only JDBC compliant way to establish a known state after an Oracle Batch Error--as far as I know.

Answer 4

Since the specification doesn't seem to mandate it (as clearly shown by the Javadoc), any "forced" continuation would have to be done on per-driver basis. A simple standard-compliant workaround would be to check the getUpdateCounts() returned array and "re-run" the batch for those statements which failed. You can make this approach a bit more sophisticated by putting in a logic for the number of retries.

Sure, this seems a bit messy (keeping track of the "batch" added and then checking the output) but would work across all databases and driver implementations. Just a thought...