Aug 6, 2015

Textual description of firstImageUrl

Difference between SYS_REFCURSOR and REF CURSOR.

Migrate your entire SQL architecture into the cloud and experience the ease of comfort to remotely monitor/access databases from anywhere on any device(PC/Mac/android/iOS) with trending citrix vdi from CloudDesktopOnline.com. Learn more about MS Azure and managed azure services by visiting one of the leading cloud hosting providers – Apps4Rent.
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

4 comments:

  1. Replies
    1. Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on a day-to-day basis. big data projects for students But it’s not the amount of data that’s important.Project Center in Chennai

      Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Corporate TRaining Spring Framework the authors explore the idea of using Java in Big Data platforms.

      Spring Training in Chennai

      The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

      Delete