PL/SQL Ref Cursor in Oracle Database

By Manish Sharma

How to create 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.


How to create PL/SQL Weak Ref Cursor in Oracle Database


So, don’t we have any alternative way of fetching data of different datatype using ref cursor which doesn’t required 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 PL/SQL 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 PL/SQL 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 ]

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 coming tutorial we will learn what is Sys Ref Cursor in Oracle Database. Thanks and have a great day!





SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

SQL Script used in Video and in this article in on GIT REPOSITORY

How do you like this blog? Is there anything you want us to improve? Tell us, what you feel on our Facebook page and on our Twitter .


  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn

         View Manish Sharma's profile on LinkedIn