Radhapur beach on Havelog Island,Andaman and Nicobar Islands

Musical fountain of Eco park, New Town kolkata part 1

Understanding Ref Cursors with Examples part 1

Today we are going to discuss about system reference cursor or ref cursor. I have been working in IT industry for last 7 years and I have seen many developers who avoid using this very powerful tool as their understanding of ref cursor is not as clear as simple cursor. So in this edition we will try to explore various aspects of ref cursor and how can we use those features in daily programming which will make our life much easier.

First question which I would like to answer before going in detailed discussion is what is ref cursor.

System reference cursors or Ref cursors are pointers to the Executed Query in an area called  process global area (PGA).

Second question is what is cursor variable? Cursor variable is a variable that points to the reference of a Cursor. A cursor variable (especially weak cursor variable, discussed later) can be opened for any number of queries. User can use a cursor variable multiple times within a same execution unit.

Some of the biggest benefit of cursor variable is written below

       Programmer can use the ref cursor as a parameter to other pl/sql units.

      Programmer can assign the pointer of one cursor to another cursor variable. Like other variables cursor variables can be assigned values using an assignment operator (See the last example).

But before using ref cursor in program all programmers should keep in mind the below points

      A ref cursor involves additional database round tip. While the ref cursor is returned to the      client, the actual data is not returned until client opens the ref cursor and try to read it.

      Secondly, you cannot update the database by ref cursor.

       Lastly, Ref cursor is not backward scrollable. Data can be fetched only in forward directions.

Types of Ref Cursor:-

The Syntax for creating a system Referenced Cursor or Ref Cursor is as follows

TYPE  cursor_name IS REF CURSOR [ RETURN return_type ].

The portion which exists within the brace is an optional part of ref cursor declaration.

Based on the existence of return clause there are two types of Ref Cursor

1>      Strong Ref Cursor :- This type of ref cursors have always a return type.

For example

TYPE v_emp IS REF CURSOR RETURN emp%ROWTYPE;

v_emp_rec   v_emp;

This type of Ref Cursor can only fetch emp%rowtype data structure. This cursor will not work on any other data structure. The advantage of a strong type is that the compiler can determine whether or not the developer has properly matched up the cursor variable FETCH statements with it’s cursor objects query lists.

For Example:-

DECLARE

  TYPE v_emp IS REF CURSOR RETURN emp%ROWTYPE;

  v_emp_ref v_emp;

  emp_rec   emp%ROWTYPE;

BEGIN

  OPEN v_emp_ref FOR SELECT * FROM emp;

  LOOP

    FETCH v_emp_ref INTO emp_rec;

    EXIT WHEN v_emp_ref%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(emp_rec.empno||'--'||emp_rec.ename||'--'||emp_rec.job||'--'||emp_rec.mgr);

  END LOOP;

END;

2>     Weak Ref Cursor :- This type of ref cursors does not have return type.

For example

TYPE v_emp_ref IS REF CURSOR;

OR

v_emp_ref  SYS_REFCURSOR; (SYS_REFCURSOR IS a predefined weak REF CURSOR type).

This type of Ref Cursor can be associated with any query. As it does not return any kind of particular data structure it can be associated with different queries which have different kind of result set. It is more flexible than the Strong Ref Cursor.

For example

DECLARE

  v_emp_ref SYS_REFCURSOR;

  emp_rec   emp%ROWTYPE;

  dept_rec  dept%ROWTYPE;

BEGIN

  DBMS_OUTPUT.PUT_LINE('----------------------------------------');

  DBMS_OUTPUT.PUT_LINE('----- Fetching Data from emp table-----');

  DBMS_OUTPUT.PUT_LINE('----------------------------------------');

  OPEN v_emp_ref FOR SELECT * FROM emp;

  LOOP

    FETCH v_emp_ref INTO emp_rec;

    EXIT WHEN v_emp_ref%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(emp_rec.empno||'--'||emp_rec.ename||'--'||emp_rec.job||'--'||emp_rec.mgr);

  END LOOP;

  CLOSE v_emp_ref;   -- Good practice code

  --

  DBMS_OUTPUT.PUT_LINE('----------------------------------------');

  DBMS_OUTPUT.PUT_LINE('----- Fetching Data from Dept table-----');

  DBMS_OUTPUT.PUT_LINE('----------------------------------------');

  --

  OPEN v_emp_ref FOR SELECT * FROM dept;

  LOOP

    FETCH v_emp_ref INTO dept_rec;

    EXIT WHEN v_emp_ref%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(dept_rec.deptno||'---'||dept_rec.dname||'--'||dept_rec.loc);

  END LOOP;

END;

And the result we got is

----------------------------------------

----- Fetching Data from emp table-----

----------------------------------------

7400--SMITH--CLERK--7902

7369--SMITH--CLERK--7902

7499--ALLEN--SALESMAN--7698

7566--JONES--MANAGER--7839

7654--MARTIN--SALESMAN--7698

7698--BLAKE--MANAGER--7839

7782--CLARK--MANAGER--7839

7788--SCOTT--ANALYST--7566

7839--KING--PRESIDENT--

7844--TURNER--SALESMAN--7698

7876--ADAMS--CLERK--7788

7900--JAMES--CLERK--7698

7902--FORD--ANALYST--7566

7934--MILLER--CLERK—7782

----------------------------------------

----- Fetching Data from Dept table-----

----------------------------------------

25---MANUFACTURING--KOLKATA

10---ACCOUNTING--NEW YORK

20---RESEARCH--DALLAS

30---SALES--CHICAGO

40---OPERATIONS--BOSTON

Please observe that the single ref cursor is used to query two different tables in two different queries. Secondly, when you are associating a new result set with a cursor variable that was previously used in an OPEN FOR statement and you did not explicitly close the cursor variable, then the underlying cursor remain open. It is not mandatory but you should always close the cursor variable before reusing them with another result set. Lastly, declaration of cursor variable does not create a cursor object. Programmer must use the OPEN FOR syntax to create a new cursor object and assign it to cursor variable.

Similarities with Static Cursor:-

While using Ref Cursor programmer can always use the basic four attributes with exactly the same syntax as for a static cursor. They are as follows

1>     REF_CUR%ISOPEN

2>     REF_CUR%FOUND

3>     REF_CUR%NOTFOUND

4>     REF_CUR%ROWCOUNT

With the following example the points will be clarified. We will modify our previous example a little bit.

SET SERVEROUTPUT ON SIZE 20000;

DECLARE

  TYPE v_emp IS REF CURSOR RETURN emp%ROWTYPE;

  v_emp_ref v_emp;

  emp_rec   emp%ROWTYPE;

BEGIN

  OPEN v_emp_ref FOR SELECT * FROM emp;

  ------

  IF v_emp_ref%ISOPEN THEN

    DBMS_OUTPUT.PUT_LINE('v_emp_ref cursor OPEN');

  END IF;

  ------

  DBMS_OUTPUT.PUT_LINE('Rowcount--->'||v_emp_ref%ROWCOUNT);

  ------

  LOOP

    FETCH v_emp_ref INTO emp_rec;

    EXIT WHEN v_emp_ref%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(emp_rec.empno||'--'||emp_rec.ename||'--'||emp_rec.job||'--'||emp_rec.mgr);

    DBMS_OUTPUT.PUT_LINE('Rowcount--->'||v_emp_ref%ROWCOUNT);

    IF v_emp_ref%FOUND THEN

      DBMS_OUTPUT.PUT_LINE('v_emp_ref cursor found');

    END IF;

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Final Rowcount--->'||v_emp_ref%ROWCOUNT);

END;

RESULT:-

anonymous block completed

v_emp_ref cursor OPEN

Rowcount--->0

7400--SMITH--CLERK--7902

Rowcount--->1

v_emp_ref cursor found

7369--SMITH--CLERK--7902

Rowcount--->2

v_emp_ref cursor found

7499--ALLEN--SALESMAN--7698

Rowcount--->3

v_emp_ref cursor found

7566--JONES--MANAGER--7839

Rowcount--->4

v_emp_ref cursor found

7654--MARTIN--SALESMAN--7698

Rowcount--->5

v_emp_ref cursor found

7698--BLAKE--MANAGER--7839

Rowcount--->6

v_emp_ref cursor found

7782--CLARK--MANAGER--7839

Rowcount--->7

v_emp_ref cursor found

7788--SCOTT--ANALYST--7566

Rowcount--->8

v_emp_ref cursor found

7839--KING--PRESIDENT--

Rowcount--->9

v_emp_ref cursor found

7844--TURNER--SALESMAN--7698

Rowcount--->10

v_emp_ref cursor found

7876--ADAMS--CLERK--7788

Rowcount--->11

v_emp_ref cursor found

7900--JAMES--CLERK--7698

Rowcount--->12

v_emp_ref cursor found

7902--FORD--ANALYST--7566

Rowcount--->13

v_emp_ref cursor found

7934--MILLER--CLERK--7782

Rowcount--->14

v_emp_ref cursor found

Final Rowcount--->14

Please observe one thing that at the very beginning of the execution the ROWCOUNT is 0 because at that time no row were fetched from the underlying table.

ROWTYPE_MISMATCH EXCEPTION:-

Some time programmer who is accustomed to use Strong Ref Cursor may get it. It is an exception which tell programmer that the data structure of query is different than the return type of the Ref Cursor itself.

DECLARE

  --v_emp_ref SYS_REFCURSOR;

  TYPE v_emp IS REF CURSOR RETURN emp%ROWTYPE;

  v_emp_ref v_emp;

  emp_rec   emp%ROWTYPE;

  PROCEDURE Return_Dept_Row(v_emp IN OUT SYS_REFCURSOR)

  IS

  BEGIN

    OPEN v_emp FOR SELECT * FROM dept;

  END Return_Dept_Row;

BEGIN

  Return_Dept_Row(v_emp_ref);

  LOOP

    FETCH v_emp_ref INTO emp_rec;

    EXIT WHEN v_emp_ref%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(emp_rec.empno||'--'||emp_rec.ename||'--'||emp_rec.job||'--'||emp_rec.mgr);

  END LOOP;

EXCEPTION

  WHEN ROWTYPE_MISMATCH THEN

    DBMS_OUTPUT.PUT_LINE('Within ROWTYPE_MISMATCH: Reason for Error-->'||SQLERRM);

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Within Others: Reason for Error-->'||SQLERRM);

END;

Assigning one Ref Cursor to another Ref Cursor variable :-

As ref cursor is just another kind of variable programmer can assign the reference of one ref cursor variable to another just using an assignment operator.

For example

DECLARE

  v_emp_ref     SYS_REFCURSOR;

  v_emp_sec_ref SYS_REFCURSOR;

  emp_rec       emp%ROWTYPE;

  emp_rec_sec   emp%ROWTYPE;

BEGIN

  OPEN v_emp_ref FOR SELECT * FROM emp;

  LOOP

    FETCH v_emp_ref INTO emp_rec;

    EXIT WHEN v_emp_ref%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(emp_rec.empno||'--'||emp_rec.ename||'--'||emp_rec.job||'--'||emp_rec.mgr);

    --

    v_emp_sec_ref := v_emp_ref;

    FETCH v_emp_ref INTO emp_rec_sec;

     DBMS_OUTPUT.PUT_LINE(emp_rec_sec.empno||'--'||emp_rec_sec.ename||'--'||emp_rec_sec.job||'--'||emp_rec_sec.mgr);

    CLOSE v_emp_sec_ref;

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Final Rowcount--->'||v_emp_ref%ROWCOUNT);

END;

OutPut is:-

ORA-01001: invalid cursor

ORA-06512: at line 9

01001. 00000 -  "invalid cursor"

*Cause:   

*Action:

7400--SMITH--CLERK--7902

7369--SMITH--CLERK—7902

Please observe that even v_emp_ref and v_emp_ref_sec is two different ref Cursor variable and both ref Cursor variable has fetched two different set of data from emp table because the pointer to the current record stays with the cursor object. It does not stays with any ref cursor variable.

During execution when we have close v_emp_ref_sec cursor variable it also closed the ref cursor v_emp_ref.