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. This raises the concern that – 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.
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. 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. We don’t want to spend the expensive resources in retrieving all the unnecessary information. Can we use this same ref cursor for that? Let’s find out.
The answer to that question is No, we cannot.
Because that Strong Ref Cursor was created using Table Based Record Datatype. When we create a table based record datatype Oracle engine creates a composite data structure with fields corresponding to each column of the specified table. 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.
To understand it more clearly please refer to PL/SQL tutorial 34 on how to initialize record datatype. There I have explained the workings of record datatype in detail.
If you want to try it by yourself then you can download the code from here.
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. 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. 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 and is used for storing the data returned by the cursor.
Now let’s come to the execution section.
On successful execution this code should show you the salary with the formatted string. Download this script from here and try executing it by yourself.
That’s it for this tutorial. Do make sure to Share this blog on your social media & help others learn. Thanks & have a great day!
You can DOWNLOAD SQL script and presentation used in the Video and in this article.