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

Strong Ref Cursors With Table Based Record Datatype

As we learnt in the introduction to Ref Cursors that there are two types of PL/SQL Ref Cursors in Oracle Database.

  1. Strong Ref Cursors and
  2. Weak Ref Cursors

We will explore all the concepts of both these types of Ref Cursors in this series. With this blog we will start with the first one that is the Strong Ref Cursors with table based record datatype and with user defined record datatype.

What is PL/SQL Strong Ref Cursors in Oracle Database?

A ref cursor which has fixed return type is called a Strong Ref Cursor in Oracle Database. Because of fixed return type, strong ref cursors can only be used selectively. For instance with those SELECT statements that return the result whose datatype matches with the one that you have fixed during cursor’s declaration.

Can we use Strong Ref Cursor with any SELECT statement in Oracle Database?

No, we cannot use Strong Ref Cursors with any SELECT statement. This is because of the fixed ‘Return Type’.

Nonetheless it can only be used with those SELECT statements that return the result whose datatype matches with the ‘Return Clause’ of the cursor.

Can we use any PL/SQL datatypes for declaring our Strong Ref Cursor?

No, we cannot. Moreover, the return type of a strong ref cursor must always be a Record Datatype.  It can either be a Table Based Record datatype or a User Defined Record Datatype.

Example of Strong Ref Cursors With Table Based Record Datatype

Let’s write an example. Here we will create a Strong Ref Cursor with Table Based Record Datatype.

 SET SERVEROUTPUT ON
 DECLARE
    	/*Create Ref Pointer Type*/
	TYPE	my_RefCur	IS REF CURSOR RETURN 	employees%ROWTYPE;
	/*Create Cursor Variable*/
	cur_var my_RefCur;
	rec_var     employees%ROWTYPE;
 BEGIN
	OPEN cur_var FOR SELECT * FROM employees WHERE employee_id = 100;
	FETCH cur_var INTO rec_var;
	CLOSE cur_var;
	DBMS_OUTPUT.PUT_LINE ('Employee '||rec_var.first_name||' has salary '||rec_var.salary||'.');
END;
/

Creation of a Ref Cursor is a two-step process.

Create a ref pointer type.

First we need to create a ref pointer type. Thus, using the TYPE statement we create a Ref Cursor Type pointer just like in the above code. In this statement you first write the keyword TYPE followed by the name of your ref cursor. Thereafter you have to write a reserved phrase IS REF CURSOR. It will tell the compiler that we are creating a type which is REF CURSOR. Followed by that you have to specify the RETURN clause.

For example

TYPE	my_RefCur	IS REF CURSOR RETURN 	employees%ROWTYPE;

Create a Cursor Variable

In the second step we create a cursor variable. In order to create a cursor variable you have to first write the name of your variable followed by the name of your Ref Cursor. Thereafter this variable will be used to refer to the Ref Cursor over which it is created.

For Example

cur_var my_RefCur;

Apart from the Cursor Variable in the above code we also have an extra variable. This is again of table based record datatype and designed using ‘Employees’ table. Moreover this variable will hold the data fetched from the cursor.

rec_var     employees%ROWTYPE;

Execution Section

In the execution section of our code we have four executable statements. Let me explain these to you.

  1. OPEN FOR statement

The first statement is the OPEN FOR statement. It associates the SELECT statement with Cursor’s variable and opens the cursor for the statement. Furthermore it also looks for all the resources required for processing the SELECT statement.

  1. FETCH statement

As we discussed in PL/SQL tutorial 26. The process of retrieving data from the cursor is called fetching. Therefore here we are fetching the data from the Ref Cursor into the record variable ‘Rec_Var’.

  1. Close Statement

Once we are done with our cursor then it is advisable to close it so that our Oracle Engine / Server can relinquish all the resources associated with it. That is exactly what we have done in the third statement. Using CLOSE keyword followed by the cursor variable we closed our ref cursor.

  1. Output statement

Fourth statement is a DBMS_OUTPUT statement. It will display back the first name and the salary of the employee with employee id 100.

You can watch PL/SQL tutorial 33 in order to learn the workings of a table based record variable.

Therefore In Conclusion…

In the above code we created a PL/SQL Strong Ref Cursor with the name ‘my_RefCur’ which will return a result table based record datatype. Furthermore this table based record datatype is supported on the Employees table of HR schema. Thus before executing this program we need to make sure that we are connected to the HR schema of our database.

Moreover if you learn better by watching video then here’s one for you. Go ahead & check it out.

That is the tutorial on how to create PL/SQL strong Ref Cursors using table based record datatype in Oracle Database. Hope you learnt something new. Additionally, please do make sure to share this blog on your social media with your friends.

Thanks for visiting. Stay tuned as in the next tutorial we will learn how to create strong ref cursor with user defined record variable.

Have a great day!

RELATED ARTICLESMORE FROM AUTHOR