Java not releasing oracle cursors

Question

I'm working on the following code below, (edited for clarity), that is giving me a few problems with open cursors in Oracle. Basically I am trying to select data from the DB and for each row returned there is 0 or more rows of sub data to be selected and appended to the record. This is currently being achieved by calling out to another function while populating the main data set to read the sub data. Which works fine for low volumes of rows, less than 1000. While this is the normal operating range that the users will use it is possible they will request all rows which could be in the order of 10's of thousands of rows. Doing a large volume select results in the ORA-01000: maximum open cursors exceeded error. If I run the code and query v$open_cursors it is possible to see the cursor count clocking up until it falls over.

If I comment out the line calling the sub function it works fine, the cursor count in v$open_cursors just fluctuates up and down by a few counts.

I noticed that the main function is passing it's connection object through to the sub function, and thought this might be causing the resulting statements and resultsets to stay open while the connection was still open even though they are closed by the code. So I have tried changing the code so each function gets it's own connection from the pool and closes it when done, but this made no difference to the cursors.

I could up the number of cursors, but a) that just masks the problem, b) I have to stick it up to a stupidly high number to get it to work, and c) I don't want to release flawed code!

However I have run out of ideas on how to get the code to release the cursors.

public ArrayList getCustomerSearchResult(Connection con) throws AnException {
    ResultSet rst = null;
    PreparedStatement stmt = null;
    ArrayList resultList = new ArrayList();

    String sql = "----  The search SQL string --- ";

    try {
        stmt = con.prepareStatement(sql);
        rst = stmt.executeQuery();

        while(rst.next()) {
            DataDTO data = new DataDTO();

            data.setSomeData(rst.getString("...."));

            // ##### This call is where the problem lies #####
            data.setSomeSubDataAsAnArrayList(getSubDataForThisRow(data.getId(), con));

            resultList.add(data);
        }

    } catch(Exception e) {
        throw new AnException("Error doing stuff", e);
    } finally{
        try{
          rst.close();
          stmt.close();
          rst = null;
          stmt = null;
        }catch(Exception ex){
            throw new AnException("Error doing stuff", ex);
        }
    }
    return resultList;
}

public ArrayList getSubDataForThisRow(String Id, Connection con) throws AnException {
    ResultSet rst = null;
    PreparedStatement stmt = null;
    ArrayList resultList = new ArrayList();

    String sql = "----  The search SQL string --- ";

    try {
        stmt = con.prepareStatement(sql);
        stmt.setString(1, Id);
        rst = stmt.executeQuery();

        while(rst.next()) {
            SubDataDTO data = new SubDataDTO();

            data.setSomeData(rst.getString("...."));

            resultList.add(data);
        }

    } catch(Exception e) {
        throw new AnException("Error!", e);
    } finally{
        try{
            rst.close();
            stmt.close();
            rst = null;
            stmt = null;
          }catch(Exception ex){
              throw new AnException("Error!", ex);
          }
      }

    return resultList;
} 

Answer 1

Are you using a connection pool? It may be caching some PreparedStatements when you think you close them.

To check if you're in this case, try to (temporarily) use non prepared statements or disable the connection pool.

Answer 2

JDBC drivers can choke on having multiple result sets on a single connection going at once. I would suspect that this is causing some buggy behavior on Oracle's JDBC driver (I have certainly seen it make issues in others - including just closing the first result set on you, which Oracle obviously isn't doing). I would be much better to get a connection to the header rows, read all of your objects, put them in a Collection, and then iterate back through them and read the detail objects with separate result sets.

Although the JDBC spec doesn't state any obligations on the JDBC driver with regards to this, the JDBC-ODBC bridge explicitly only allows one active statement per connection, so other JDBC drivers are certainly free to have similar restrictions (such as only one open result set per connection).

Answer 3

Eek, this looks like PowerBuilder code from 1999. Performing multiple selects for the children is an antipattern. You need to do this in fewer calls to the DB... it is way to chatty.

Since you are on Oracle, you could try to use a connect by prior to retrieve the child rows with the parent rows - all at once. That's the best solution.

If you can't get the connect by prior, you could combine the calls into an in(id1,id2,...,idN) clause and retrieve them in chunks.

Also might take a look at your concurrency settings on the resultset. Maybe you have a scrollable resultset?

However you solve it, I would be worried about blowing out the VM and getting OOM. You'll need a row limit for the search results.

Answer 4

You could try preparing both the main ("master") and the sub ("detail") statements beforehand:

PreparedStatement masterStatement = masterConnection.prepareStatement("...");
PreparedStatement detailStatement = detailConnection.prepareStatement("SELECT ... WHERE something = ?");


ResultSet masterResults = masterStatement.executeQuery();
while (masterResults.next()) {
    detailStatement.setInt(1, ...);

    ResultSet detailResults = detailStatement.executeQuery();
    try {
        while (detailResults.next()) {
        }
    } finally {
        detailResults.close();
    }
}