pl/sql strong ref cursor with user record datatype by manish sharma

Strong Ref Cursor With User Defined Record Datatype

In the previous tutorial we learnt how to create a PL/SQL strong ref cursor using table based record datatype variable. There I demonstrated the use of strong ref cursor to handle the data from all the columns of employees table. Consequently, this raises a concern. What if we want the data from some specific column of the table? That’s exactly what we will be addressing in today’s tutorial.

However, before moving ahead in this tutorial I suggest you to get acquainted with the knowledge of PL/SQL Ref Cursors. Specially the Strong Ref Cursor and User Defined Record Datatype. Furthermore, for your convenience I am providing all the necessary links here:

The previous tutorial successfully showed how a PL/SQL Strong ref cursor retrieved and handled data from all the columns of a row. But what if we want the data from a specific column of a row? Let’s say we just want to see the E-mail or the salary of an employee. Consequently, we don’t want to spend the expensive resources in retrieving all the unnecessary information. So, can we use this same ref cursor for that? Let’s find out.

Can we use the Strong Ref Cursor we created in the previous tutorial to retrieve data from a specific column?

The answer to that question is No, we cannot.

Because that Strong Ref Cursor was created using Table Based Record Datatype. To illustrate, when we create a table based record datatype, Oracle engine creates a composite data structure. This structure is replete with fields corresponding to each column of the specified table.

Furthermore, all these fields automatically get assigned with the same name and same datatype as of the columns of the base table. But when it comes to initializing a record using a specific column we have to do all this work manually.

In order to understand it more clearly please refer to PL/SQL tutorial 34. There I have discussed how to initialize record datatype and its workings in detail.

So, what is the solution to this problem?  

We can easily solve this problem. We can create a PL/SQL strong ref cursor with User-Defined Record Datatype variable.

Example: PL/SQL Strong Ref Cursor with User Defined Record Datatype Variable.

For instance, we want to create a strong ref cursor with the SELECT statement. Which returns only the salary of the employee whose employee id is 100.

SET SERVEROUTPUT ON;
DECLARE
	--Create User-Defined Record Datatype
    TYPE my_rec IS RECORD (
        emp_sal employees.salary%TYPE
        );
	--Declare Strong Ref Cursor
    TYPE RefCur IS REF CURSOR RETURN my_rec;
    cur_var REFCUR;
	--Another anchored datatype variable for holding data
    at_var  employees.salary%TYPE;
BEGIN
   OPEN cur_var FOR SELECT salary FROM employees WHERE employee_id = 100;
    FETCH cur_var INTO at_var;
    CLOSE cur_var;
    DBMS_OUTPUT.PUT_LINE ('Salary of the employee is '||at_var);
END;
/

So, let’s break this code and see what is happening here.

Declaration Section

DECLARE
    TYPE my_rec IS RECORD (
        emp_sal employees.salary%TYPE
        );
    TYPE RefCur IS REF CURSOR RETURN my_rec;
    cur_var REFCUR;
    at_var  employees.salary%TYPE;

Here is our declaration section. In the first three lines of this section we created our user-defined record datatype with the name my_rec. Furthermore, this user defined record datatype has only one field which is emp_sal. Emp_sal is an anchored datatype field which is designed over the salary column of the employees table. Because emp_sal is of anchored datatype thus it will automatically get assigned the datatype and the data width of the base column which in this case is salary.

Right after that we declared our PL/SQL Strong Ref Cursor with the name “RefCur”. The return type of this strong ref cursor is “my_rec”. Followed by cursor declaration we created a cursor variable with the name cur_var. Additionally, this variable is used for referring to the strong ref cursor ahead in the code.

Apart from cursor variable we also have another variable declared in this section. This variable is “at_var” this is again an anchored datatype variable. It is used for storing the data returned by the cursor.

Now let’s come to the execution section.

Execution Section

BEGIN
    OPEN cur_var FOR SELECT salary FROM employees WHERE employee_id = 100;
    FETCH cur_var INTO at_var;
    CLOSE cur_var;
    DBMS_OUTPUT.PUT_LINE('Salary of the employee is '||at_var);
END;
/

This execution section has four executable line of codes. Let’s see what those are.

Line 1: Open For statement

This statement dynamically Opens the mentioned cursor. Thereafter it attaches the SELECT statement that is specified right after the FOR keyword.

In our case this statement is opening the strong ref cursor using the cursor variable “cur_var” – a SELECT statement. Which is returning the data only from the “salary column” of the employees table.

Line 2: Fetch statement

This statement will fetch the data from the ref cursor and store it into the variable “at_var”. Equally important is to make sure that the datatype of the data fetched and that of the variable into which the data is getting stored must match. Otherwise there will be an error.

Line 3: Close statement

The third line is a close statement. Closing a cursor that you are done with is a good practice.

Line 4: Output statement

Finally, the last statement of the execution section is the DBMS_OUTPUT statement. And it is displaying the salary of the employee back to the user.

These four lines complete the execution section of this PL/SQL block. Furthermore the successful execution of this code should show you the salary with the formatted string.

Are one of those who learn better by watching video? Then here’s the video tutorial on Strong ref cursor with user defined record datatype.

That’s it for this tutorial. Do make sure to Share this blog on your social media & help others learn. You can subscribe to the YouTube channel for more interesting tutorials. Thanks & have a great day!