Ignore bad statements in jdbc batch

Question

I have code to do a batch insert into a SqlServer or Oracle table. How can I tell JDBC to
A) Keep going if one of the insert statements fails
B) Inform me which one had the issue

sql.withBatch("""Insert Into ${job.table}($columnNames)Values(${values})""") { stmt ->
    data.each{ Map<String, String> row ->
        stmt.addBatch(newMap)
    }
}

Currently, if one of the rows has an issue, it successfully inserts everything that came before it, and stops inserting rows afterwards. And it'll tell me the error message, but not what data caused the error. A solution in either java or groovy is fine.

My question is not the same as the other one. His issue is specifically around avoiding inserting duplicates. I need to prevent the batch from failing because an insert failed for any reason. If the solution turns out to be sql-related, I'd need both Tsql and PL/SQL solutions (the other post is specific to postgresql)

score 2 · Accepted Answer · answered May 06 '17 at 18:05

2

With Oracle there is no way to tell the driver to continue the execution of the batch in case of an error (your A isn't possible). But you can catch the BatchUpdateException and call getUpdateCounts() to know which of the lines were successfully executed. The exception will also return the error message from the Database server.

answered May 06 '17 at 18:05

Jean de Lavarene

3,461
1
20
28

This is my solution. I'm catching the exception, logging it, skipping the bad row, and recursing with a sublist, based off the UpdateCount. Thanks! – Steve May 08 '17 at 16:02

score 0 · Answer 2 · answered Oct 13 '18 at 17:23

Following can be implemented in Oracle

1) Create the error logging table.

BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'dest');
END;
/

2) append following statement to your sql

LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;

3) Now to look into errros

SELECT ora_err_number$, ora_err_mesg$
FROM   err$_dest
WHERE  ora_err_tag$ = 'INSERT';

Ignore bad statements in jdbc batch

2 Answers2