<h1>An Overview and Clear Understanding of Oracle REF CURSOR</h1>

I'll discus shortly how we can use ref cursor easily, with some easy example which will make a very clear concept about ref cursor. After reading this topic you will be very familiar with the ref cursor. So let's start.

First I'll go for an overview of ref cursor like what is ref cursor? How data is fetched using ref cursor? What is the use of ref cursor? and some key information about ref cursor

The name "Ref Cursor" can be confusing because a ref cursor variable is not a cursor. The REF CURSOR is a data type in the Oracle PL/SQL language. It represents a cursor or a result set in Oracle Database which acts as a pointer or a handle to a result set on the database. We can use ref cursor as OUT parameter of procedure and Function. Remember ref cursor do not hold actual data like variable or array or oracle explicit cursor. It's just a pointer or a handle to the result set of a query. What does it mean? This point will be clear later in this topic.

Oracle introduces REF CURSOR type in oracle 7.3. Oracle 9i introduced the predefined SYS_REFCURSOR type, that's mean we no longer have to define our own REF CURSOR types and I'll use this SYS_REFCURSOR type in my example. Oracle 11g allows the two-way conversion between ref cursors to DBMS_SQL cursors. Oracle 12c allows implicit statements results, similar to that seen in Transact-SQL. I'll discus this two point later here in another post.

N.B. I used oracle database 11g R2, toad 9.5 and SQL*PLUS for the entire example in this post.
The following example shows the syntax of ref cursor.

 DECLARE
 v_ref_cur sys_refcursor;
 BEGIN
    OPEN v_ref_cur FOR 
    'select empno, ename,sal 
      from emp
         where deptno =:deptno ' 
       USING p_deptno;
 RETURN v_ref_cur;
 END;

Code Anatomy: First I declare a variable v_ref_cur which data type is ref cursor. Then opening the variable with 'OPEN v_ref_cur' and in FOR clause I writing the query. Remember query must be enclosed in single quotation mark.
At this point I'll write a simple function that opens a ref cursor

  CREATE OR REPLACE FUNCTION get_emps_info (p_deptno IN NUMBER)
  RETURN sys_refcursor
  IS
  v_ref_cur sys_refcursor;
  BEGIN
   OPEN v_ref_cur FOR 
   'select empno, ename, sal from emp
    where deptno =:deptno' 
   USING p_deptno;
   RETURN v_ref_cur;
  END;
 

Here v_ref_cur is a variable which data type is ref cursor. Now we can look into v_ref_cur with following code in SQL*PLUS.

SQL> var v_rc_handle refcursor;

[Declare variable as refcursor type to hold information,which will be returned from our ref cursor.]

N.B. SQL*PLUS refcursor is similar to pl/sql sys_refcursor.

SQL> exec : v_rc_handle := get_emps_info (20);

[Calling our get_emps_info function and getting the ref cursor handle in v_rc_handle variable]

SQL> print v_rc_handle;

[Printing v_rc_handle variable]

That means our ref cursor retunes employees information. It's great!! But at first I told ref cursor do not hold data, so how it showing data. Now it is time to make it clear. Look at the following command.We just trying to print our v_rc_handle variable again

SQL> print v_rc_handle;

SP2-0625: Error printing variable " v_rc_handle"

Why SQL*PLUS can't print the v_rc_handle variable now? Because of at this point the ref cursor doesn't hold any data; v_rc_handle just a pointer to the query result set. Our first print command fetched all the rows and then it closes the cursor and when we tried to print the ref cursor again, we got an error because SQL*Plus find the cursor as it is open and couldn't print it again.

Now I'll try it a little bit more details, so that it can be clearer. I'll use some PL/SQL code.

[+] [-]  Show/Hide Code

  
 

DECLARE
 v_rc_handle sys_refcursor;
 v_empno NUMBER;
 v_ename VARCHAR2 (20);
 v_mgr NUMBER;
 v_sal NUMBER;
 
 BEGIN
  v_rc_handle := get_dept_emps (10); 
  -- This returns an open cursor
  
  LOOP
   FETCH v_rc_handle 
   INTO v_empno, v_ename, v_mgr, v_sal;
   EXIT WHEN v_rc%NOTFOUND; 
   -- Exit the loop when we've run out of data
   DBMS_OUTPUT.put_line (v_empno||', '||v_ename|| );
  END LOOP;
  
  CLOSE v_rc;
 END;
 

Code Clarification :

v_rc_handle is a variable which will act as handle to our ref cursor and variable v_empno, v_ename , v_mgr v_sal will hold our original value. Whit 'v_rc_handle:= get_dept_emps (20);' this line of code we getting the ref cursor handle. Then in loop we are fetching our data. 'EXIT WHEN v_rc%NOTFOUND;' this line will end the loop and Exit the loop when all record of ref cursor will be fetch

Is it possible to use a oracle ref cursor in a SQL query and How?

Now question is, is it possible to use a ref cursor in a SQL query and How? Answer is yes possible. To achieve it first we need to create a type structure. Since SQL cannot read/access PL/SQL table structures so the type must be a database object.

CREATE OR REPLACE TYPE emptype AS OBJECT ( empno NUMBER, ename VARCHAR2 (10), mgr NUMBER, sal NUMBER );

This code will create a object type named emptype. Now with this object type we will create a table type which can hold data like a database table.

Create or replace type emp_tabtyp as table of emptype;

This emp_tabtyp type is a structure to hold a record. To populate this type with data which coming from the ref cursor we will create a function using our table type emp_tabtyp as follows:

[+] [-]  Show/Hide Code

 

CREATE OR REPLACE FUNCTION populate_emps_info 
 (deptno IN NUMBER := NULL)
 RETURN emp_tabtyp
 IS
 
 v_emptype emp_tabtyp := emp_tabtyp (); 
 -- Its a local table structure
 v_ref_cur sys_refcursor;
 v_cnt NUMBER := 0;
 v_empno NUMBER;
 v_ename VARCHAR2 (10);
 v_mgr NUMBER;
 v_sal NUMBER;
 
 BEGIN
     v_ref_cur := get_emps_info (deptno);
     
     LOOP
      FETCH v_ref_cur 
      INTO v_empno, v_ename, v_mgr, v_sal;
   EXIT WHEN v_rc%NOTFOUND;
   v_emptype.EXTEND;
   v_cnt := v_cnt + 1;
   v_emptype (v_cnt) := 
   emptype (v_empno, v_ename, v_mgr, v_sal);
   END LOOP;
   
  CLOSE v_rc;
  RETURN v_emptype;
 END;
 

Function populate_emps_info calls the get_emps_info function that opens the ref cursor, then looping through and fetching each row and populating our SQL type structure. When all rows are fetched, the ref cursor is closed and the SQL table structure is passed back from the function. So now we have something in an structure that SQL understands, we should be able to query directly from it..

SQL> select * from table(populate_emps(30));

We've successfully get the ref cursor handle and used it to fetch the data back. Remember again A ref cursors is not a cursor or data holder. Once opened, they are simply a pointer to a query that has yet to fetch data.

So, this is all about ref cursor for now. I think it will help you to understand ref cursor. Enjoy it. If you have any query\comment please leave it in comment box. I'll be very pleased to reply you. Thank you.