PL/SQL SYS_REFCURSOR In Oracle Database

As we learnt in the introduction to the ref cursor that there are three types of PL/SQL ref cursor in Oracle Database.

  1. Strong ref cursor
  2. Weak ref cursor and
  3. SYS_REFCURSOR

So far in this series we have covered the concepts of Strong as well as Weak ref cursor. The only type of cursor which still untouched is PL/SQL SYS_REFCURSOR in Oracle Database. Which we will learn in this blog.

What is PL/SQL SYS_REFCURSOR?

SYS_REFCURSOR is a predefined weak ref cursor which comes built-in with the Oracle database software.

How will it being a pre-defined ref cursor helps us?

SYS_REFCURSOR is a predefined weak ref cursor which means we do not have to define it into our code like we did with the explicit weak ref cursor in the previous tutorial. As we learnt in that tutorial that creating a weak ref cursor is a two-step process. First you have to create the “weak ref cursor type” and then you have to create “a cursor variable” using that ref cursor.

When using SYS_REFCURSOR, you just have to create a cursor variable and nothing else. SYS_REFCURSOR completely minimizes the first step where you create the weak ref cursor type.

So Sys_Refcursor is a built-in ref cursor, where can we find its definition?

You can find out the definition of PL/SQL SYS_REFCURSOR in a script named “stdspec.sql”. You can locate this script into the directory whose path is as follow. 

%ORACLE_HOME% \RDBMS\ADMIN\stdspec.sql

SYS_REFCURSOR definition will look something like this:

pl/sql sys ref cursor in oracle database by manish sharma

What type of Ref Cursor is a SYS_REFCURSOR?

As mentioned above in the definition a SYS_REFCURSOR is a Weak Ref Cursor.

Now that you have cleared almost all the doubts about SYS_REFCURSOR. I just want to ask you one last question.

Info: There is no space between REF and CURSOR. It is a single word “RefCursor”.
Wrong: SYS_REF CURSOR
Correct: SYS_REFCURSOR

Why would someone declare a weak ref cursor when we already have a predefined one?

That is actually a very good question. The answer to this question is No, we do not need to create a weak ref cursor explicitly. Oracle minimized the need of creating a weak ref cursor by shipping SYS_REFCURSOR as a part of standard package since Oracle Database 9i though the option of creating one is still available.

It is like a personal choice, if someone wants to create a weak ref cursor then they can. If not, then they have SYS_REFCURSOR always at their disposal. My dear friend Steven Feuerstein did a very good blog post answering this question which you can read here.

Let’s see a very easy example demonstrating how to use a PL/SQL SYS_REFCURSOR in Oracle Database.

Example: How to create PL/SQL SYS_REFCURSOR in Oracle Database?

SET SERVEROUTPUT ON;
DECLARE
    --Declare cursor variable of SYS_REFCURSOR type
    cur_var SYS_REFCURSOR;
    
    --Declare variables for holding data
    f_name  employees.first_name%TYPE;
    emp_sal employees.salary%TYPE;
BEGIN
OPEN cur_var FOR SELECT first_name, Salary FROM employees WHERE employee_id = 100;
    FETCH cur_var INTO f_name, emp_sal;
    CLOSE cur_var;
    DBMS_OUTPUT.PUT_LINE(f_name ||' '||emp_sal);
END;
/

You can watch the video tutorial on the YouTube channel for detailed explanation of the above code.

Hope you enjoyed reading and learnt something new. Please do share this blog on your social media with your friends. Thanks and have a great day.