.zugiart

Software Engineering, buddhism, and everything else in between.

Handling Oracle cursors in JDBC

by zen on August 17, 2010, 3 comments

Summary

It is common practice for Oracle stored procedure to pass cursors to calling client. The client then needs to guarantee that the cursors are handled and closed properly, otherwise overtime the open cursor will accumulate into an ORA-1000 error (“ORA-1000 Maximum Number of Cursors Exceeded”)

In Java world, cursor translates as ResultSet. In the case where an open cursor is passed from a stored procedure to a Java code (over JDBC), the code must work with the ResultSet and not forget to close() it after it’s done with it. However, this is often overlooked, because when ResultSet is garbage collected, the close() is not called and as a result, the open cursor remains open in the Oracle server.

This fault is surprisingly common amidst Java programmer – and the reason is because inexperienced Java programmers are usually spoiled by Garbage Collection, and many of them failed to develop the strict habit of clearing resource after they are used.

Therefore, Java programmers should always remember to explicitly close() any resource that is explicitly open such as file, and in this case, Oracle database cursors (ResultSet).

Example

This is an example of stored procedure using cursor:

PROCEDURE A_PROC(P_1 IN VARCHAR2, P_OUT_VAL OUT SYS_REFCURSOR) IS
    -- body of execution ---
    OPEN P_OUT_VAL ...
    -- exception handling --
    EXCEPTION WHEN OTHERS THEN
    -- don't forget to close cursor here
END A_PROC;

on stored procedures that passes cursor to the client, it is important to first agree on the way cursors will be handled on exception cases. The best case is usually for the stored procedure to close all open cursor that it can close in the case of exception. In the case where open cursor is successfully passed, the Java/JDBC code need to handle it like so:

CallableStatement cstmt = conn.prepareCall("SQL...");
ResultSet rs = null;
try {
   rs = // obtain result set here
}catch(Exception ex){
   // handle exception here
}
finally{
   // ensure result set (cursors) are closed
   rs.close();
   // ensure statement is closed here
   cstmt.close();
}

Offline ResultSet

What if you need to use the result set outside the context of the operation? Then use CachedRowSet – Oracle provide a concrete implementation that will work fine, called OracleCachedRowSet. Like so:

// this is where the live cursor is
ResultSet rs;
// define offline container & populate it.
CachedRowSet crs = new OracleCachedRowSet();
crs.populate(rs);

But beware:

  • ResultSet can be huge, CachedRowSet is convenient but beware of extracting too many data into the cached rowset. That cache lives in memory, don’t blow it out.
  • ResultSet resource lifecycle still need to be maintained as per usual, close if when you’ve done reading.

Corner case: Cursor Spike

It is also possible to encounter ORA-1000 error when a JDBC Connection (Oracle Session) is shared amongst multiple thread of execution. A cursor limit is usually imposed on a given Session – sometimes it is possible that due to high usage of the shared session, the cursor ‘spikes’ and exceeded this limit.

This will give a false impression and could cause database developers and JDBC developers to go round chasing the cause for days, while in fact it is not a code problem, but rather session capacity / provisioning problem.

The solution to this is to implement good logging in both end (JDBC & DB side) to isolate the cause, working with DBAs to categorically record session usage and seeing patterns in which the session is being used.

The ideal solution is to control the Connection sharing via Connection Pools.

  • Alwyn Lvv

    very useful information, especially the last paragraph regarding the cursor spike, really solve my problem.

  • http://twitter.com/zenfeed zen

    good that it helps!

  • daniel

    Hi,

    we have same problem, but seems it to be more compicated. because…
    Our support procedures returns more than one cursor. When java code fails after second or third resultset then never opens next resultsets and never closes them. And we have cursol leak on connection (connection lifetime is maintained by JEE container). So we close everithing we open (statement, resultset), commit or rollback each transaction, but still have cursors leaks.

    What with this?

    For us is very hard to read rest of results of procedure when we are in cach. Code failed for some reson, is in exceptional situation and in this situation is very hard to continue in reading resultsets (next exceptions can occurs an so on).

    any ideas?

    Thanks.