CODEWITHSUNDEEP
javadatabaseoracle11grelational-databasestruts-1
Susie
Susie

Reputation: 5138

Batch processing fails in Oracle. Find out which statement in the batch is causing it

I am extracting data from excel sheet and inserting them into my oracle table. The database is setup in a way that when executing a batch statement, if any insert statement in the batch fails, all the other statements in the batch are not executed. So my problem is how can I find out which row of data is actually causing it, so I can send a message to the user with the row number of the data that's causing the problem? 

Connection con = null;
    PreparedStatement pstmt = null;
    Iterator iterator = list.iterator();
    int rowCount = list.size();
    int currentRow = 0;
    String sqlStatement = "INSERT INTO DMD_VOL_UPLOAD (ORIGIN, DESTINATION, DAY_OF_WEEK, VOLUME)";
    sqlStatement += " VALUES(?, ?, ?, ?)";
int batchSize==1000;
for(currentRow=1; currentRow<=rowCount; currentRow++){
            ForecastBatch forecastBatch = (ForecastBatch)      iterator.next();                                 
            pstmt.setString(1, forecastBatch.getOrigin());
            pstmt.setString(2, forecastBatch.getDestination());
            pstmt.setInt(3, forecastBatch.getDayOfWeek());                                          
            pstmt.setInt(4, forecastBatch.getVolumeSum());

            pstmt.addBatch();
        if(i % batchSize == 0){
                updateCounts = pstmt.executeBatch();
                con.commit();
                pstmt.clearBatch();
                session.flush();
                session.clear();
            }
        }

Upvotes: 2

Views: 4237

Answers (2)

mazaneicha
mazaneicha

Reputation: 9427

java.sql.Statement's javadoc says that executeBatch() throws BatchUpdateException (a subclass of SQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.

The method getUpdateCount() of java.sql.BatchUpdateException "..Retrieves the update count for each update statement in the batch update that executed successfully before this exception occurred..."

If none of this works, you will probably have to fall back to executing and committing each statement (within this particular batch) individually until you hit an error.

Upvotes: 1

Keith Randall
Keith Randall

Reputation: 23265

executeBatch returns an integer array containing the counts of all the rows modified by each statement in the batch. I think negative numbers are used to indicate errors. You should be able to figure out which ones failed using this return value.

http://docs.oracle.com/javase/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame6.html

Upvotes: 1

Related Questions