Why PL/SQL Weak Ref Cursor 

In the previous tutorial we learnt how to fetch data of different datatypes from different columns using the strong ref cursor. The strong ref cursors have a limitation that they have fixed return type which can only be a record datatype. However fixed return type makes a strong ref cursor less error prone in the application development. But this definitely requires an extra step of creating a user defined record datatype.

So, don’t we have any alternative way of fetching data of different datatype using ref cursor which doesn’t require the creation of user defined record datatype? Can’t we create a ref cursor which doesn’t have a fixed return type? Let’s find out the answers of these questions in this blog.

What is a PL/SQL Weak Ref Cursor in Oracle Database?

A ref cursor which does not have a fixed return type is called a Weak Ref Cursor.

Can we use Weak Ref Cursor with any SELECT statement or not?

Yes, we can. Because weak ref cursors don’t have a fixed return type like strong ref cursors thus they are open to all types of SELECT statements. This also makes them most frequently used ref cursors in comparison to others.

I guess now you have got the answers to the above questions. PL/SQL Weak Ref Cursor is the alternative way of fetching data of different datatypes. As weak ref cursor doesn’t have a fixed return type thus there is no need of creating a separate record datatype. Now that we have cleared all our doubts it’s time to see an example which will help you in learning how to create PL/SQL weak ref cursors in Oracle Database.

Syntax of Weak Ref Cursor

TYPE ref_cursor_name IS REF CURSOR;

Example: How to create Weak Ref Cursor in Oracle Database

SET SERVEROUTPUT ON;
DECLARE
    /*Declare Weak Ref Cursor*/
    TYPE wk_RefCur IS REF CURSOR;
    /*Declare Cursor Variable of ref cursor type*/
    cur_var wk_RefCur;
    
     /*Declare two "Anchored Datatype Variable" for holding data from the cursor*/
    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;
/

Go ahead and try executing this program to see what the output will be. If you want you can also download this script from the GIT repository. Here are the Links [Script / Git Repository ]

If you learn better by watching videos, then here’s the video tutorial on PL/SQL weak ref cursor.

That is the tutorial on how to create PL/SQL weak ref cursor in Oracle Database. So make sure to subscribe to the YouTube channel. In the upcoming tutorial we will learn what is Sys Ref Cursor in Oracle Database. Thanks and have a great day!