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.

Answer 3

You could try this: Start with batches of 50 or 100. (Choose a size so they have a good probablity to be processed successfully). Those which fail, process one-by-one.

Other possibility: Disable the constraints, load the data, delete those rows which violate the constraints.

Answer 4

i should check first to see if there is a constraint violation, than insert that record if the contraints is not violated.

Answer 5

I was looking some solution on the line of "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." Basically I wanted to know if this can be done with Oracle JDBC driver.

However a variety of answers have been proposed (most/all of which I had already considered) 1) Disabling the constraints/load data/remove offending rows/repeat this many times 2) Do all the checking before loading data 3) Decrease the batch size to 50 - 100.

Unfortunately my checking cannot be done before loading and making batch size to be 50 or 100 means taking more time to do the 5M rows I have (infact the total time increased to a few hours instead of 40 mins with a batch size of 1000). I have resorted to keeping the batch size of 1000 ACCEPTING THE problem as is and put the code under a "while" loop and do the job until we fill up all rows.

Like I said, since there is NO WAY WITH ORACLE BATCH JDBC to proceed after first failure, the answer to this question will be "NOT DOABLE" and just accept the constraints and document the fact that this tool takes about 40 mins to complete :)

Answer 6

Could you try the Oracle merge-when-not-matched statement? Example: http://www.idevelopment.info/data/Oracle/DBA_tips/SQL/SQL_14.shtml

Answer 7

Have an exceptions table and make sure your proc never raises exception but saves all exceptions in database. Once all done, query exceptions table and see records could not go through.