Use Ref Cursor in Java and Close it

In one of our production databases, I observed from v$open_cursor that some sessions from weblogic application server could have about 2000 opened cursor count for a particular ref cursor. Usually the weblogic servers are recycled every night. I can observe the count increasing from 0 to about 2000 before recycle each day.

This database has been crash every week from ORA-600 [17074]. In the trace file, the SQL of the process that crashes the database is shown to be that ref cursor. So the cursor was thought to be contributed into the crash at least. (Oracle Support suggests that the bug in the server combined with the condition in our code caused the crash). However, recently the database crash problem has mysteriously disappeared without any sound reasons.

Development team claimed that they close that cursor in their Java code, but I really doubted it as the count is still increasing every day. I am afraid that it is a bomb that will explode some day again.

I have used Java several years ago when I did some course works in school. However, since then, I have never touched Java. I assigned myself a task to simulate the cursor count increasing symptom. It took me some time to figure out how. I even asked that in some forum, but did not get what I want. I eventually found this AskTom post and this site helpful.

The test is done in the Oracle sample schema HR and in a 10G database.

First of all, I created a function in a package that returns a ref cursor. Given a Department ID, the function returns a ref cursor pointing to a list of last names and first names of the employees belonging to that department.

The function looks like:

function sfGetEmpDept
( pDeptID in EMPLOYEES.DEPARTMENT_ID%type)
return csGetResultSet is

csGetEmp csGetResultSet;

begin
open csGetEmp for
SELECT e.last_name, e.first_name, e.department_id
FROM employees e
WHERE e.department_id = pDeptID
ORDER BY e.last_name;

return csGetEmp;
end sfGetEmpDept;

Secondly, I figured out how to use this ref cursor in a Java program through JDeveloper. In the Java code, I called a method getEmpDept 10 times.

em.showOpenCnt( conn, "*** Before anything" );
for(int i = 10;  i <=100; i = i + 10 ) {        
emlist = em.getEmpDept(i);
System.out.println( "Dept ID: " + i +
" Number of employee is " + emlist.size()  );
}
em.showOpenCnt( conn, "*** After everything" );

There is a line in the method getEmpDept which controls whether close the result set or not:

rs.close();

I measure the open curosr count before and after call getEmpDept. The results are as expected:

1. Uncomment out rs.close(), i.e. we close the ref cursor after use it.

Get the Number of emplyee in a Department
*** Before anything
1 opened cursors current
-----------------------
Dept ID: 10 Number of employee is 1
Dept ID: 20 Number of employee is 2
Dept ID: 30 Number of employee is 6
Dept ID: 40 Number of employee is 1
Dept ID: 50 Number of employee is 45
Dept ID: 60 Number of employee is 5
Dept ID: 70 Number of employee is 1
Dept ID: 80 Number of employee is 34
Dept ID: 90 Number of employee is 3
Dept ID: 100 Number of employee is 6
*** After everything
1 opened cursors current
-----------------------
Process exited with exit code 0.

2. Comment out rs.close(), i.e. we don't close the ref cursor in every call
*** Before anything
1 opened cursors current
-----------------------
Dept ID: 10 Number of employee is 1
Dept ID: 20 Number of employee is 2
Dept ID: 30 Number of employee is 6
Dept ID: 40 Number of employee is 1
Dept ID: 50 Number of employee is 45
Dept ID: 60 Number of employee is 5
Dept ID: 70 Number of employee is 1
Dept ID: 80 Number of employee is 34
Dept ID: 90 Number of employee is 3
Dept ID: 100 Number of employee is 6
*** After everything
11 opened cursors current
-----------------------
Process exited with exit code 0.

It is obvious that without rs.close, every time we call the Java method, we will increase the count by 1. With this simulation, I feel a little bit confident to question the development team again as to whether that cursor is really closed

Here can be found the code for the ref cursor and Java class.

[Ed. Sep 28, 2009 -]:
Find another AskTom thread that is very related to this topic

[Updated, Feb 22, 2010 -]:
We had a near-crash siutation by same ORA-600 17074 last Monday night, database was completely hung. Development team eventually identified the problem in a packaged PL/SQL procedure, which returns three ref cursors when called by Java code , however developers only needs to use one of them in the application, so they use it and close that one, but leaving the other two open. After fix, i.e. close the other two also. I observed the max open cursor per session is not even exceeding  100.