what is ref cursor?

What is a PL/SQL ref cursor? What is the purpose and where the situation we use Ref cursor in our application?

A ref cursor in Oracle PL/SQL is much like an ordinary PL/SQL cursor in that it acts as a pointer to the result set of the cursor with which it is associated. However, the difference is that a ref cursor can be assigned to different result sets whereas a cursor is always associated with the same result set. Cursors and ref cursors are not interchangeable.

The real purpose of ref cursors is to be able to share cursors and result sets between the client and the Oracle server or between different subroutines. For example you might open a cursor in an Oracle Forms client and then continue working with the cursor on the server or you might open a cursor in say a Java program and then continue working with it in a PL/SQL stored procedure.

Ref cursors also come in two variants: strongly typed and weakly typed depending on how likely you are (or want to) reuse a cursor variable. Weak ref cursor types can be associated with any query whereas strong ref cursor types can only be associated with cursors of the same type.

Let's look at a couple of examples.


DECLARE
 TYPE wkrefcurty IS REF CURSOR;
        -- weak ref cursor type
 my_cur wkrefcurty;
 dept departments%ROWTYPE;
 BEGIN
  OPEN my_cur FOR SELECT * FROM departments;
  FETCH my_cur INTO dept;
  CLOSE my_cur;
 END;

Note that despite the cursor being weakly typed, the variable to hold the results must be strongly typed. In other words you can't fetch into a variable of the weak cursor's row type.

The following declaration generates a PL/SQL compilation error (PLS-00320: the declaration of the type of this expression is incomplete or malformed).

DECLARE
 TYPE wkrefcurty IS REF CURSOR;
 my_cur wkrefcurty;
 rslt my_cur%ROWTYPE; -- generates a PL/SQL 320 error
 BEGIN
  OPEN my_cur FOR SELECT * FROM departments;
  FETCH my_cur INTO rslt;
  CLOSE my_cur ;
 END;
/
ORA-06550: line 4, column 7:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 7:
PL/SQL: Item ignored
ORA-06550: line 7, column 20:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 7, column 2:

Also with a PL/SQL weak ref cursor if you fetch into a variable of incompatible type this causes an an exception to be raised by Oracle at run time. For example

DECLARE
 TYPE wkrefcurty IS REF CURSOR;
 -- weakly-typed ref cursor
 my_cur wkrefcurty;
 emp employees%ROWTYPE;
BEGIN
  OPEN my_cur FOR SELECT * FROM departments;
  FETCH my_cur INTO emp;
  CLOSE my_cur;
END;
/
DECLARE
*
ERROR at line 1:
ORA-06504: PL/SQL: Return types of Result Set variables or query do not match
ORA-06512: at line 7

In the above example we attempted to fetch a row from the departments table into a variable that matches the employee table structure. This causes Oracle to raise an exception.

With a strong PL/SQL ref cursor type, errors like this are reported by the Oracle PL/SQL compiler at compile time, as in the following example.

DECLARE
 TYPE myrefcurty IS REF CURSOR RETURN employees%ROWTYPE; -- strong ref cursor type
 my_cur myrefcurty;
 emp employees%ROWTYPE;
BEGIN
 OPEN my_cur FOR SELECT * FROM departments; -- can't do this
 FETCH my_cur INTO emp;
 CLOSE my_cur;
END;
/
ORA-06550: line 6, column 18:
PLS-00382: expression is of wrong type
ORA-06550: line 6, column 2:
PL/SQL: SQL Statement ignored

As long as the result set matches the cursor declaration we can reuse the cursor variable in our PL/SQL code for new queries as much as we like as in the following examples.

Example of cursor re-use using a strongly-typed ref cursor.

DECLARE
 TYPE myrefcurty IS REF CURSOR RETURN employees%ROWTYPE; -- strong ref cursor type
 my_cur myrefcurty;
 emp employees%ROWTYPE;
BEGIN
 OPEN my_cur FOR SELECT * FROM employees;
 FETCH my_cur INTO emp;
 CLOSE my_cur;

 OPEN my_cur FOR
  SELECT * FROM employees
  WHERE employee_id = 1;
 FETCH my_cur INTO emp;
 CLOSE my_cur;

 OPEN my_cur FOR
  SELECT * FROM employees
  WHERE employee_id > 1000;
 FETCH my_cur INTO emp;
 CLOSE my_cur;

 OPEN my_cur
  FOR SELECT * FROM employees
  WHERE department_id = 1;
 FETCH my_cur INTO emp;
 CLOSE my_cur;

END;
The following example shows the re-use of a weakly-typed ref cursor.

DECLARE
 TYPE wkrefcurty IS REF CURSOR;
     -- weakly-typed ref cursor
 my_cur wkrefcurty;
 emp employees%ROWTYPE;
 dept departments%ROWTYPE;
BEGIN
 OPEN my_cur FOR SELECT * FROM departments;
 FETCH my_cur INTO dept;
 CLOSE my_cur;

 OPEN my_cur FOR SELECT * FROM employees;
 FETCH my_cur INTO emp;
 CLOSE my_cur;
END;
As mentioned earlier, we can open a ref cursor in one Oracle PL/SQL procedure and fetch from it in another as in the following example.

CREATE OR REPLACE PACKAGE ref_cursor_demo IS
 TYPE wkrefcurty IS REF CURSOR;
     -- weakly-typed ref cursor
 PROCEDURE open_cursor (the_cursor OUT wkrefcurty);
 PROCEDURE fetch_cursor (the_cursor IN OUT wkrefcurty);
END ref_cursor_demo;

CREATE OR REPLACE PACKAGE BODY
ref_cursor_demo IS

 PROCEDURE open_cursor (the_cursor OUT wkrefcurty) IS
 BEGIN
  OPEN the_cursor FOR
   SELECT * FROM departments
   ORDER BY department_name DESC;
END open_cursor;

 PROCEDURE fetch_cursor (the_cursor IN OUT wkrefcurty) IS
  dept departments%ROWTYPE;
BEGIN
 FETCH the_cursor INTO dept;
 dbms_output.put_line('1st department is '||dept.department_name);
END fetch_cursor;
END ref_cursor_demo;

DECLARE
 my_cur decl_cursor.wkrefcurty;
BEGIN
 ref_cursor_demo.open_cursor(my_cur);
 ref_cursor_demo.fetch_cursor(my_cur);
END;
/

1st department is Treasury

That gives you an idea of what you can do with ref cursors.