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.