avoid jdbc batch insert failure if one record failed to insert in oracle

Question

The Oracle JDBC driver throws a BatchUpdateException if an error occurs in the middle of the batch. Using NamedParameterJdbcDaoSupport to insert data as below

getJdbcTemplate().batchUpdate(query, dataList, 1000,
  new ParameterizedPreparedStatementSetter<MyObject>() {
     @Override
     public void setValues(final PreparedStatement ps, final MyObject bucket) throws SQLException {
       ps.setString(1, bucket.getInit());
       ps.setString(2, bucket.getNbr());                  
    }
});

I'm running into BatchUpdateException because of unique key constraint, we are processing millions of records so we can't go to database to check unique key.

Is there anyway that batch insert won't fails if one record failed.

Is there some way you can scrub your data before even sending it to Oracle such that a failure like this would be very improbable? — Tim Biegeleisen
as we are processing millions of records, we don't know which record is duplicate.. if it is null pointer or like exceptions we can handle in code only — User4567
I don't have an answer then. Maybe use smaller batches, such that if one batch fails, you don't have to rollback everything. — Tim Biegeleisen
yeah i will take your suggestion.. try to remove failed record from batch and insert again. — User4567

pohart pohart · Accepted Answer · 2018-09-26T14:01:35

You want to use the IGNORE_ROW_ON_DUPKEY_INDEX hint. add a comment after the insert, like this.

insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX(table_name, unique_index_name) */
       (...)

But if you're inserting a million rows this way you're likely better off using sqlldr.

avoid jdbc batch insert failure if one record failed to insert in oracle

1 Answers