CODEWITHSUNDEEP
javaconnectioncursorbatch-processingora-01000
Tsubasa
Tsubasa

Reputation: 137

ORA-01000: maximum open cursors exceeded for batch update PreparedStatement

I was using batch update prepared statement over millions of record but eventually encountered the following error in runtime after execution of >400k of records.

java.sql.SQLException: ORA-01000: maximum open cursors exceeded

I don't know if this is caused by the following code

try{    
conn = ConnectionManager.getConnection();
// turn off autocommit
conn.setAutoCommit(false);

PreparedStatement stmt = conn.prepareStatement(query);

//for each of the records to be updated
for(int k=0;k<objects.length;k++) { 

    stmt.setString(1, objects[k].getValueA());
    stmt.setString(2, objects[k].getValueB());

    stmt.addBatch();

    //running batch execute on every 10000 records
    if(k%10000 == 0 || k == objects.length-1) {
        // submit the batch for execution
        int[] updateCounts = stmt.executeBatch();

        conn.commit();

        if(k < objects.length-1)
        {
            stmt.close();
            stmt = conn.prepareStatement(query);
        }
    }
}
}catch( Exception e ) {
    e.printStackTrace();
}finally {
    if(conn!=null){
        conn.close();
    }
}

whereby the PreparedStatement is closed and replaced with the new PreparedStatement after the connection is committed. I am not sure if repetitive commit of the same connection may be causing this problem.

Can anyone suggest a solution for the problem or suggest better architecture to handle this batch update of prepared statement.

P/S: the error line actually pointing to delete statement execution, but I don't think this is the root cause, as previously before the batch update prepared statement code is added this problem is not existed.

try {
    if ( hasData ) {
        conn = ConnectionManager.getConnection();
        CommonStore.deleteRecords( conn, queryStr ); //the error point to this line
        hasData = false;
    }
}catch( Exception e ) {
    e.printStackTrace();
}finally {
    if(conn!=null){
        conn.close();
    }
}

Thanks for any suggestion

Upvotes: 3

Views: 4065

Answers (1)

Sujay
Sujay

Reputation: 6783

One thing that I observed is this:

if(k < objects.length-1)
{
    stmt.close();
    stmt = conn.prepareStatement(query);
}

Instead of closing your stmt object, I would suggest reusing it. This is just an overhead that can be avoided.

I don't think it would serve any purpose to close and re-open the same PreparedStatement. Also, you can consider calling PreparedStatement.clearParameters() after addBatch()

Upvotes: 0

Related Questions