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
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.
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:
Example for cursor re-uses
Example for opening the ref cursor in one Oracle PL/SQL procedure and fetching in another PL/SQL block
Calling the procedure in PL/SQL block
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;
