What Is PL/SQL Ref Cursors In Oracle Database?

When it comes to performance, PL/SQL Ref Cursors in Oracle have always proven themselves superior to static cursors. Because of the ability of a single ref cursor to get associated with multiple SELECT statements in a single PL/SQL block. In addition, this reduces the need for writing separate explicit cursors. Moreover it makes them a highly efficient, flexible and robust feature of PL/SQL language. So let’s explore Ref Cursors in PL/SQL and learn more about it.

What are PL/SQL Ref Cursors in Oracle Database?

Ref Cursor is an acronym of Reference to a Cursor. It is a PL/SQL datatype using which you can declare a special type of variable called Cursor Variable.

Are Cursor Variable and Ref Cursor connected to each other?

That is a very good question. I saw in many books that both these names are used interchangeably.  However both of them are two separate yet inter-dependent topics.

On one hand cursor variable is like a pointer which refers to different context area in SGA. Whereas Ref Cursor is a datatype which holds a cursor value.

Then, how are Cursor Variable and Ref Cursors inter-dependent on each other?

As mentioned above, Cursor Variables are declared with the help of Ref Cursors. Consequently, we can say that in Oracle Database cursor variables exist in the form of Ref Cursors.

Definition of Cursor Variable
Using all the above information we can define cursor variable as: A variable of Ref Cursor Type is called Cursor Variable.

What is the syntax for Declaring Ref Cursor?

Here is the generalized syntax for declaring Ref cursors in Oracle Database:

DECLARE
 TYPE [cursor_variable_name] IS REF CURSOR [RETURN (return_type)];

Syntax can be modified and used depending on what type of Ref Cursors you want to use in your application. Having said that let’s see the types of Ref Cursors available in Oracle Database.

How many types of PL/SQL ref cursors do we have?

There are two types of Ref Cursors in PL/SQL. These are:

1. Strong Ref Cursor and
2. Weak Ref Cursor

 

What is Strong Ref Cursor in Oracle Database?

Any Ref Cursor which has a fixed return type is called a Strong Ref Cursor.

Moreover such ref cursors can only be used with some SELECT statements. In addition, the result of SELECT statement’s datatype should match with the one that was fixed during the strong cursor’s declaration.

Syntax of Strong Ref Cursors in PL/SQL

Here is the strong ref cursor’s syntax.

DECLARE
	TYPE cursor_variable_name IS REF CURSOR 
RETURN (return type);

Return clause plays a very vital role in declaring a Ref Cursor. It restricts its scope. And makes your Ref Cursor limited to only those SELECT statements that return the result whose datatype matches with the one you specified in the RETURN clause while declaring it.

Also the return type of a Ref Cursor must always be of Record Datatype. It can either be record structure of a table or user defined record structure.

What is a Weak Ref Cursor in Oracle Database?

In contrast to strong ref cursors, weak ref cursors are those which do not have any return type. In other words, those ref cursors which do not have fixed return type are called weak ref cursors.

Since weak Ref Cursors do not have any fixed return type thus they are open to all SELECT statements. And this makes them one of the most used Ref Cursors in PL/SQL.

Syntax of Weak Ref Cursors in PL/SQL

DECLARE
	TYPE ref_cursor_name IS REF CURSOR;

Except from the missing return clause the syntax is pretty much similar to the strong ref cursors.

Well, my dear friends if you are one of those who want to refrain from doing all the hard work of creating a Cursor Variable then Oracle PL/SQL has an option for you. This option will serve as an implicitly created cursor variable to you. So, let’s see what it is.

Sys_RefCursor

Sys Ref cursor is an Oracle built in cursor variable. It declares a weak ref cursor and that too without declaring the ref pointer type. Mostly it is used as a generic cursor which can be passed as an argument to a stored sub program.

That’s it for this tutorial on the introduction to PL/SQL Ref Cursors in Oracle Database. Stay tuned as in the upcoming tutorials we will do some practical demonstration of the Ref Cursor in Oracle PL/SQL.

You can also refer to the video tutorial on the same topic on my YouTube channel for more details.

Thanks for stopping by. Have a great day!