SYS_REFCURSOR Vs. REF CURSOR

Difference between SYS_REFCURSOR and REF CURSOR?

REF CURSOR and SYS_REFCURSOR type is used interchangeably in PL/SQL. SYS_REFCURSOR is predefined REF CURSOR defined in standard package of Oracle so that we need not to write code again and again 😊

Its declaration will be located @ %ORACLE_HOME%/rdbms/admin/stdspec.sql

For me it is: E:\app\Admin\product\11.2.0\dbhome_1\RDBMS\ADMIN

Now, we can simply use this Type in our code and this has already been declared and ready to use.

In the same way, we create Ref Cursor Type and then declare its variables to use in our code.

/* Ref Cursor Type Declaration*/

Type My_Ref_Type Is Ref Cursor;

Following code demonstrate the difference between them where we will be using both SYS_REFCURSOR and REF CURSOR.

CREATE OR REPLACE PROCEDURE 

FETCH_EMPLOYEES_NAMES(V_DeptNo NUMBER, V_REF OUT SYS_REFCURSOR ) 

/*Note: SYS_REFCURSOR as parameter type used here 

because it has been declared in standard package it is a ref cursor */

IS

Begin

OPEN V_REF For Select FIRST_NAME, LAST_NAME From EMP_TEST where DEPTNO = V_DeptNo;

End FETCH_EMPLOYEES_NAMES;

Using Ref Cursor

We will use the above Procedure to get the names into Ref Cursor and Display output.

DECLARE

/* Ref Cursor Type Declaration */

Type MyRefCur Is Ref Cursor;

V_Fname   VARCHAR2(10);

V_Lname   VARCHAR2(10);

V_Cur MyRefCur ;/* Declare Ref Cursor Variable*/

V_Deptno NUMBER(2) := 20;

BEGIN

FETCH_EMPLOYEES_NAMES(V_Deptno,V_Cur);

DBMS_OUTPUT.PUT_LINE('FIRST_NAME' || ' ' || 'LAST_NAME');

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

LOOP

 FETCH V_Cur INTO V_Fname, V_Lname;

 EXIT WHEN V_Cur%NOTFOUND;

 DBMS_OUTPUT.PUT_LINE(V_Fname || '      ' || V_Lname);

END LOOP;

CLOSE V_Cur;

END;

Using SYS_REFCURSOR

We will use the same Procedure to get the names into SYS_REFCURSOR and Display output.

DECLARE

V_Fname   VARCHAR2(10);

V_Lname   VARCHAR2(10);

V_Cur SYS_REFCURSOR; /* Declare SYS_REFCURSOR Variable*/

V_Deptno NUMBER(2) := 20;

BEGIN

FETCH_EMPLOYEES_NAMES(V_Deptno,V_Cur);

DBMS_OUTPUT.PUT_LINE('FIRST_NAME' || ' ' || 'LAST_NAME');

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

LOOP

 FETCH V_Cur INTO V_Fname, V_Lname;

 EXIT WHEN V_Cur%NOTFOUND;

 DBMS_OUTPUT.PUT_LINE(V_Fname || '      ' || V_Lname);

END LOOP;

CLOSE V_Cur;

END;

So, SYS_REFCURSOR is just a synonym for the REF CURSOR type.  SYS_REFCURSOR used as cursor type otherwise we would have to create a cursor type and use it every time in different situations.

Execution of the Above Codes

SQL> CREATE OR REPLACE PROCEDURE
  2  FETCH_EMPLOYEES_NAMES(V_DeptNo NUMBER, V_REF OUT SYS_REFCURSOR )
  3  /*Note: SYS_REFCURSOR as parameter type used here because it has been declared in standard package it is a ref cursor */
  4  IS
  5  Begin
  6  OPEN V_REF For Select FIRST_NAME, LAST_NAME From EMP_TEST where DEPTNO = V_DeptNo;
  7  End FETCH_EMPLOYEES_NAMES;
  8  /

Procedure created.


/*Using Ref Cursor*/
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  /* Ref Cursor Type Declaration */
  3  Type MyRefCur Is Ref Cursor;
  4  V_Fname   VARCHAR2(10);
  5  V_Lname   VARCHAR2(10);
  6  V_Cur MyRefCur ;/* Declare Ref Cursor Variable*/
  7  V_Deptno NUMBER(2) := 20;
  8  BEGIN
  9  FETCH_EMPLOYEES_NAMES(V_Deptno,V_Cur);
 10  DBMS_OUTPUT.PUT_LINE('FIRST_NAME' || ' ' || 'LAST_NAME');
 11  DBMS_OUTPUT.PUT_LINE('---------------------------------');
 12  LOOP
 13   FETCH V_Cur INTO V_Fname, V_Lname;
 14   EXIT WHEN V_Cur%NOTFOUND;
 15   DBMS_OUTPUT.PUT_LINE(V_Fname || '      ' || V_Lname);
 16  END LOOP;
 17  CLOSE V_Cur;
 18  END;
 19  /

FIRST_NAME LAST_NAME
---------------------------------
SURYA      Baluni
RAJAN      Kumar
MANU       Pillai
KARAN      Sareen

PL/SQL procedure successfully completed.


/*Using SYS_REFCURSOR*/
SQL> DECLARE
  2  V_Fname   VARCHAR2(10);
  3  V_Lname   VARCHAR2(10);
  4  V_Cur SYS_REFCURSOR; /* Declare SYS_REFCURSOR Variable*/
  5  V_Deptno NUMBER(2) := 20;
  6  BEGIN
  7  FETCH_EMPLOYEES_NAMES(V_Deptno,V_Cur);
  8  DBMS_OUTPUT.PUT_LINE('FIRST_NAME' || ' ' || 'LAST_NAME');
  9  DBMS_OUTPUT.PUT_LINE('---------------------------------');
 10  LOOP
 11   FETCH V_Cur INTO V_Fname, V_Lname;
 12   EXIT WHEN V_Cur%NOTFOUND;
 13   DBMS_OUTPUT.PUT_LINE(V_Fname || '      ' || V_Lname);
 14  END LOOP;
 15  CLOSE V_Cur;
 16  END;
 17  /

FIRST_NAME LAST_NAME
---------------------------------
SURYA      Baluni
RAJAN      Kumar
MANU       Pillai
KARAN      Sareen

PL/SQL procedure successfully completed.