executeBatch behaviour in case of partial failure

Question

I have a java 1.6 application which use batch insert for inserting records in Oracle db using jdbc driver. As you know on Statement object there is a method called executeBatch() which we use for batch updates. It has a return type of int array which has result of execution for each record in it. But it also throws BatchUpdateException in case of error and we can get result int array from that too. My question is in what error situations I should expect BatchUpdateException and when I should expect there is no exception thrown but for some records I get failure.

Note: Question is spesifically for Oracle JDBC. And to make it more clear, I have seen situations that after executing executeBatch() I did not get BatchUpdateException however some of the insert statements failed. My question was about in what situation that can occur ?

This is the return javadoc of Statement.executeBatch() method. According to the general opinion here when one entry fails, execution throws BatchUpdateException then in which condition we can expect some entries in return array failed.

      * @return an array of update counts, with one entry for each command in the
 *         batch. The elements are ordered according to the order in which
 *         the commands were added to the batch.
 *         <p>
 *         <ol>
 *         <li> If the value of an element is >=0, the corresponding command
 *         completed successfully and the value is the update count for that
 *         command, which is the number of rows in the database affected by
 *         the command.</li>
 *         <li> If the value is SUCCESS_NO_INFO, the command completed
 *         successfully but the number of rows affected is unknown.
 *         <li>
 *         <li> If the value is EXECUTE_FAILED, the command failed.
 *         </ol>
 * @throws SQLException
 *             if an error occurs accessing the database
 */
public int[] executeBatch() throws SQLException;

Define "for some records I get failure". For example if you do an update, then no rows affected is not a failure, it simply means no rows were updated. — Mark Rotteveel
Here I have just Insert statements. So I dont see some of the records in db but I dont see any exception trace either. — cacert
@cacert, do your inserts have a where clause that prevents double insertion? I'm thinking something like WHERE NOT EXISTS (...) — Brett Walker
good point, but insert statements doesnt have this. INSERT INTO FTX.ESI_FLTR_02_ (TB_TRANSFERSEQUENCE,TB_LINENUMBER,TB_CONTENT,TB_STATUS,TB_LINESEQUENCE) " + "VALUES (?,?,CONCAT(?,?),?,?) — cacert

Brett Walker Brett Walker · Accepted Answer · 2015-06-16T12:11:00

Let's say that you have 5 batch update statements. The execution of each them is to update 20 records, known in advance.

The execution of the batch of update statements occurs without a BatchUpdateException, or a SQLException being thrown.

If any of the elements in the returned int array is not 20 then you known there has been unexpected behaviour. This could be seen as a failure.

EDIT

From the JavaDoc of the BatchUpdateExcpetion (The highlights are my addition)

After a command in a batch update fails to execute properly and a BatchUpdateException is thrown, the driver may or may not continue to process the remaining commands in the batch. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will have an element for every command in the batch rather than only elements for the commands that executed successfully before the error. In the case where the driver stops [ed] processing commands, the array element for any command that failed is Statement.EXECUTE_FAILED.

My understanding from this is that if any statement in the batch fails then a BatchUpadteException will be thrown.

executeBatch behaviour in case of partial failure

2 Answers