JDBC Batch Update Problem

Question

I have a slightly unique requirement with the Java-JDBC API along with Oracle Database. I have autoCommit to be default which is true for Oracle and I am using the example similar to this link.

However, when I add say 1000 batches and lets say each of them are inserts. And Let us assume that about 20 records violated some constraints, I want the remaining 980 to go COMMITTED (and henceforth visible to any other queries using any other connection) to the database and ignore the 20 records. In the above example, when one row violates any transaction then even when I commit in the catch block the transaction only commits until the first failure.

I know batch updates are to be done ONLY when you are fairly sure all rows will go through and exception processing is not one, but am planning to PATCH an existing database so some kind of "bad practices" is okay :) Any code samples will be highly appreciated.

**** MORE DETAILS ****

Using Simple insert/update is not okay since I am processing close to 3M rows so am batching every 1000 records. Simply adding 1000 inserts in loop (ignoring exceptions) takes way more time (about 5 seconds for every 1000 records) as opposed to the batch update < 300ms.

Problem: With Oracle database the Driver seems to stop at the first FAILURE, ie when 1000 rows are batched and 100th failed, I want it to go ahead till the 1000th row. Me thinks this cannot be done in JDBC (with Oracle) Like the link indicates only few databases support such feature and probably Oracle is not one

Answer 1

You can use a PL/SQL stored procedure using the SAVE EXCEPTIONS clause which will allow you to issue a bulk update and then return those rows which could not be updated. Here is a couple of example links:

http://rwijk.blogspot.com/2007/11/save-exceptions.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8912264456901

Answer 2

You should insert into a working table that does not have the constraints, then delete or fix what would be in violation and INSERT SELECT the rest over into the real table in a single SQL statement.