JDBC Batch insert exception handling to know the particular failed record

Question

I want to read a txt file which has oracle insert statements in java program and execute as a batch, If any of the record gets failed in the batch, How do i get to know the particular insert statement only got failed.

for Ex.if i have 1000 records in file and 659 record gets failed in insertion, how can i get to know the 659 record got failed in executebatch update.Is there any other process to do this please let me know.

Answer 1

There are two ways to handle the above situation either you check-in Java code or write the procedure

1. Java Code: this will either load everything or roll back the whole batch.

updateResult = preparedStatement.executeBatch();
            checkSuccessfulUpdates(updateResult);

public static void checkSuccessfulUpdates(int[] updateCounts) throws Exception {
    for (int i :updateCounts) {
         if (i == Statement.EXECUTE_FAILED) {
            throw new Exception("Not all rows were updated. Rolling back entire batch ");
        }
    }
}

2. Write a SQL procedure

Send the entire data in custom object to procedure, load it 1 by 1 by record, handle the exception within the loop, on error load the failed record in a temporary table.