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).
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
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;
rs = // obtain result set here
// handle exception here
// ensure result set (cursors) are closed
// ensure statement is closed here
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
// define offline container & populate it.
CachedRowSet crs = new OracleCachedRowSet();
- 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.