Aug 6, 2015

Difference between SYS_REFCURSOR and REF CURSOR.

REF CURSOR and SYS_REFCURSOR type is used interchangeably in PL/SQL program. With respect to functionality or interpretation by processing engine there is no visible difference between them.
However, from programmer point of view, the fundamental difference between is that - programmer has to create type of REF CURSOR(weak or strong) and its variable (called cursor variable) in their program unit (package body or anonymous block), however SYS_REFCURSOR is predefined REF CURSOR defined in standard package of Oracle located at following location in windows: %ORACLE_HOME%/rdbms/admin/stdspec.sql
where %ORACLE_HOME% = C:\oraclexe_32bit\app\oracle\product\11.2.0\server\

SYS_REFCURSOR available from Oracle 9i onwards as part of standard package and weak reference created for programmer easiness. Following declaration of SYS_REFCURSOR  from "stdspec.sql":
/* Adding a generic weak ref cursor type */
  type sys_refcursor is ref cursor;
and while variable declaration we use SYS_REFCURSOR type .

Similarly, REF CURSOR type is created by programmer as part of program unit and cursor variable is created using "cur_ref_type" type.
/*Cursor type declarator by programmer*/
type cur_ref_type is ref cursor;

Sample PL/SQL program to demonstrate differences between them, below is the stored procedure that we want to execute using both SYS_REFCURSOR and REF CURSOR.
create or replace procedure get_employees_name(
v_deptId_in NUMBER,
v_cur OUT SYS_REFCURSOR ) 
--Note: SYS_REFCURSOR as parameter type used here because   
--it has been declaredin standard package it is a ref cursor. 
IS
begin
  open v_cur for select FIRST_NAME,LAST_NAME from
        employees where DEPARTMENT_ID = v_deptId_in;
end get_employees_name;



Execution by creating a custom cursor type- REF CURSOR - notice "type custom_ref_cursor is ref cursor;" is used to create a cursor type and "v_cur custom_ref_cursor;" is creating a variable out of it.
--using refcursor --
declare
-declare ref cursor type 
type custom_ref_cursor is ref cursor;
v_fname   VARCHAR2(10);
v_lname   VARCHAR2(10);
v_cur custom_ref_cursor; -declare ref cursor variable
v_deptId_in NUMBER(2) := 90;
begin
HR.get_employees_name(v_deptId_in,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

Execution using standard cursor - SYS_REFCURSOR-  
here "type custom_ref_cursor is ref cursor;" is commented and "v_cur SYS_REFCURSOR" is creating a cursor variable using predefined cursor as SYS_REFCURSOR.
declare
--type rc is ref cursor; Not required 
v_fname   VARCHAR2(10);
v_lname   VARCHAR2(10);
v_cur SYS_REFCURSOR; 
--SYS_REFCURSOR used to create cursor variable 
v_deptId_in NUMBER(2) := 90;
begin
HR.get_employees_name(v_deptId_in,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;

Conclusion :- SYS_REFCURSOR is just a synonym for the REF CURSOR type. SYS_REFCURSOR has been created as part of standard package just to discourage boiler plate coding (in above created procedure get_employees_name- SYS_REFCURSOR  used as cursor type other wise we would have to create a cursor type and use it, assume you have 100 similar procedure or function).Thanks Oracle for saving us from hitting keyboard unnecessarily !!!

========End of post==========
Read also:
1. Difference between Weak reference type and strong reference type ?
2. Difference between explicit cursor and cursor variable - CURSOR vs REF CURSOR.

Location: Hyderabad, Telangana, India