Java JDBC error: maximum open cursors exceeded

[lozina]

I am doing some JDBC in Java and getting this error from Oracle:
ORA-01000: maximum open cursors exceeded

I am not sure I understand why.

The stack trace indicates the source is a "getString" command on a ResultSet.

Is the ResultSet creating a new cursor for every iteration? I thought it would be 1 ResultSet = 1 Open Cursor no matter how many records are being iterated. No?

Here is what the overall code "looks like" - I cannot paste the exact source:

Connection conn = null;
try {
    
    conn = datasource.getConnection();            
    
    String outerSql = 
        "select id, count(1) as recordCount from BLAH group by id";
                        
    stmt = conn.prepareStatement(outerSql);
    stmt.setLong(1, batchId);
    
    ResultSet rs = stmt.executeQuery();    

    int interval = 1000;    
    
    // This ResultSet has 97 records
    while (rs.next()) {
    
        int nextId = rs.getInt(1);
        int recordCount = rs.getInt(2);
        startRow = 1;
        
        StringBuffer sb = new StringBuffer();
        
        String selectSql = 
            "select data_column as msg from "+
            "(INNER QUERY where id = ?) "+
            "where rn between ? and ?";
        
        while (startRow <= recordCount) {
        
            stmt = conn.prepareStatement(selectSql);

            stmt.setLong(1, nextId);
            stmt.setInt(2, startRow);
            stmt.setInt(3, startRow+interval-1);
            
            ResultSet rs2 = stmt.executeQuery();            
            while (rs2.next()) {
                sb.append(rs2.getString("msg")); // <-- THIS IS WHERE THE MAXIMUM CURSOR ERROR OCCURS
            }
            rs2.close();

            
            // do what I need to do with whats in StringBuffer and then clear it for next batch
            sb = new StringBuffer();
            
            startRow += interval;
        }
    }                    
    
    rs.close();
    
} catch (Throwable e) {
    ...
} finally {
    try { conn.close(); } catch (Exception ignored) {}
    
}

Basically, I am batching a through a large amount of data 1,000 records at a time.

I have an outer select which gathers the unique ids or types of records along with their record count, then use this for an inner select which batches through 1,000 of the records for each id at a time.

The error occurs oddly in a "getString" command. I expected it to come from something like opening a new connection. It seems like getString off of a ResultSet is opening a new cursor each time. But that doesnt make sense either, because the current cursor limit on the db is 300, and I am looping through 1,000 at a time. This error doesnt happen during the first batch of 1,000, but well into the process. In this particular case it processed 58 batches, or 58,000 records, before it started a batch that caused it to fail.

I explicitly close the result sets, and close the connections. No errors are occurring other than this one. And this is not a threaded process, only one thread can be doing this process at one time. The only thing I dont do is explicitly close statements. Do I need to?

 

[MrChad]

The only thing I dont do is explicitly close statements. Do I need to?

I would try it. Also, your ResultSet and Statement close() calls should be in finally blocks.