What is ref cursor?

What is ref cursor? What is the purpose of using ref cursor?

A ref cursor is 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.

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.


Let's start with a simple function that opens a ref cursor and passes it back

CREATE OR REPLACE FUNCTION xx_reftest

   RETURN sys_refcursor

IS

   cur   sys_refcursor;

BEGIN

   OPEN cur FOR 'SELECT empno,ename FROM emp';

   RETURN cur;

END;

Now, if we look at using this through SQL*Plus we first create ourselves a ref cursor variable to accept the results of the function, and then call the function to get the ref cursor back.
SQL> var rc refcursor;
SQL> exec :rc := xx_reftest ();
PL/SQL procedure successfully completed.

Ok, so our variable ‘rc’ has our ref cursor.
If we use SQL*Plus print command will print the record set data.
SQL> print rc;

     EMPNO ENAME
---------- ----------
      7839 KING
      7698 BLAKE
      7782 CLARK
      7566 JONES
      7788 SCOTT
      7902 FORD
      7369 SMITH
      7499 ALLEN

So our ref cursor returns the rows we wanted from the employee table.

Let's look at that again.

SQL> print rc;
SP2-0625: Error printing variable "rc"

 What's happened here?  Why can't we print the data that's in our ref cursor again?
A common mistake that people believe is a ref cursor actually contains the result data from the query. In truth, the ref cursor doesn't contain any data at all; it's just a pointer to the query.
So why did the first print statement print out the results?
SQL*Plus looked at the ref cursor and saw that it was an open cursor.  As such it went into a loop, fetching each row of data from the database, using the ref cursor as its reference (pointer) to the relevant query, and displaying each row of data until it had no more rows to fetch.  Once it's fetched all the rows it closes the cursor.  That’s the power of ‘print’ command.
Therefore, when we tried to print the ref cursor a second time, we got an error because SQL*Plus looked at the cursor, saw it was not an open cursor and couldn't perform the task of printing anything.



Let's look at some more examples.

DECLARE

   TYPE xx_cur IS REF CURSOR;        --Ref cursor type declaration

   my_cur   xx_cur;                  --Declare ref cursor variable

   xx_emp   emp%ROWTYPE;

BEGIN

   OPEN my_cur FOR SELECT * FROM emp; --Assigning the record set to cursor variable

   FETCH my_cur INTO   xx_emp;

   CLOSE my_cur;

END;

From 9i onwards we can replace the below statements

TYPE xx_cur IS REF CURSOR;
   my_cur   xx_cur;      

By using the below single statement

my_cur   sys_refcursor;            


Example:

DECLARE

  

   my_cur   sys_refcursor;               --Declare ref cursor variable

   xx_emp   emp%ROWTYPE;

BEGIN

   OPEN my_cur FOR SELECT   * FROM emp; --Assigning the record set to cursor variable

   FETCH my_cur INTO   xx_emp;

   CLOSE my_cur;

END;

Example for cursor re-uses

DECLARE

   TYPE xx_cur IS REF CURSOR;

   my_cur    xx_cur;

   xx_emp    emp%ROWTYPE;

   xx_dept   dept%ROWTYPE;

BEGIN

   --cursor pointing to record set of dept table

   OPEN my_cur FOR SELECT * FROM dept;

   FETCH my_cur INTO xx_dept;

   CLOSE my_cur;

   --cursor pointing to record set of emp table

   OPEN my_cur FOR SELECT * FROM emp;

   FETCH my_cur INTO xx_emp;

   CLOSE my_cur;

END;

Example for opening the ref cursor in one Oracle PL/SQL procedure and fetching in another PL/SQL block

CREATE OR REPLACE PROCEDURE xxad_ref_test (cur OUT sys_refcursor)

AS

BEGIN

   DBMS_OUTPUT.put_line ('start of xxad_ref_test procedure');

   OPEN cur FOR SELECT   * FROM emp;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('errors:-' || SQLERRM);

END;

Calling the procedure in PL/SQL block

DECLARE

   cur1     sys_refcursor;

   xx_emp   emp%ROWTYPE;

BEGIN

   DBMS_OUTPUT.put_line ('Calling procedure');

   xxad_ref_test (cur1);

   DBMS_OUTPUT.put_line ('End of procedure calling');

   LOOP

      FETCH cur1 INTO   xx_emp;

      EXIT WHEN cur1%NOTFOUND;

      DBMS_OUTPUT.put_line (xx_emp.ename);

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Errors ' || SQLERRM);

END;