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.

Cursor Variables - Impementing Variant Records

The power of cursor variables comes from their ability to point to different cursors.
This test example comes from this doc

denis@TEST10G>
denis@TEST10G> create table emp_tab
  2  as
  3  select
  4    rownum empno,
  5    object_name ename,
  6    mod(rownum, 10) + 1 deptno ,
  7    rownum * 100 sal
  8  from all_objects
  9  where rownum < 100;

Table created.

denis@TEST10G>
denis@TEST10G>
denis@TEST10G> drop table dept_tab purge;

Table dropped.

denis@TEST10G> create table dept_tab
  2  as
  3  select
  4    rownum deptno,
  5    'd_' || rownum  deptname
  6  from all_objects
  7  where rownum <=10;

Table created.

denis@TEST10G>
denis@TEST10G>
denis@TEST10G> -- In the following package example, a discriminant is used to
denis@TEST10G> -- open a cursor variable to point to one of two different cursors:
denis@TEST10G>
denis@TEST10G> create or replace package emp_dept_data as
  2    type cv_type is ref cursor;
  3    procedure open_cv(cv     in out cv_type,
  4                      discrim  in positive);
  5  end emp_dept_data;
  6  /

Package created.

denis@TEST10G>
denis@TEST10G> create or replace package body emp_dept_data as
  2    procedure open_cv (cv in out cv_type,
  3                       discrim in positive) is
  4    begin
  5      if discrim = 1 then
  6         open cv for select * from emp_tab where sal > 2000;
  7      elsif discrim = 2 then
  8         open cv for select * from dept_tab;
  9      end if;
 10    end open_cv;
 11  end emp_dept_data;
 12  /

Package body created.

denis@TEST10G>
denis@TEST10G> -- You can call the open_cv procedure to open the cursor variable
denis@TEST10G> -- and point it to either a query on the emp_tab table or
denis@TEST10G> -- the dept_tab table.
denis@TEST10G>
denis@TEST10G> declare
  2    emp_rec emp_tab%rowtype;
  3    dept_rec dept_tab%rowtype;
  4    cv       emp_dept_data.cv_type;
  5
  6  begin
  7  --  emp_dept_data.open_cv(cv,2); -- open cv for emp_tab fetch
  8    emp_dept_data.open_cv(cv,1); -- open cv for emp_tab fetch
  9    fetch cv into dept_rec;   -- but fetch inot dept_tab record
 10                              -- which raises rowtype_mismatch
 11    dbms_output.put(dept_rec.deptno);
 12    dbms_output.put_line('  ' || dept_rec.deptname);
 13  exception
 14    when rowtype_mismatch then
 15      begin
 16        dbms_output.put_line
 17              ('Row type mismatch, fetching emp_tab data ...');
 18        fetch cv into emp_rec;
 19        dbms_output.put(emp_rec.deptno);
 20        dbms_output.put_line('  ' || emp_rec.ename);
 21      end;
 22  end;
 23  /
Row type mismatch, fetching emp_tab data ...
2  C_TS#

PL/SQL procedure successfully completed.