Overview on the usage of Cursor Variables in PL/SQL

A cursor in Oracle is a static object. It is bound to only one specific query. For example the explicit and implicit cursors are static and attached to specific queries. A cursor variable on the other hand is a pointer to a cursor. A cursor variable can hold many queries within a particular program execution.

They can be passed and returned as formal parameters to subprograms. It can also be opened in one subprogram and processed in another on server side. The main advantages of using cursor variables are encapsulation, ease of maintenance and convenient security.

While the declaration of cursor variable and open statement syntax is enhanced from the syntax used for static cursors, the cursor operations for the cursor variables is similar to that of static cursors. The cursor attributes %ISOPEN, %FOUND, %NOTFOUND and %ROWCOUNT can also be used with similar syntax as used for static cursors.

Datatype of a cursor variable is REF_CURSOR. There are two steps to be followed in creation of a cursor variable. First a referenced cursor TYPE has to be created and the actual cursor variable declared based on that type. The REF CURSOR type may be strong or weak.
The syntax for creating a referenced cursor type is

TYPE cursor_type_name IS REF CURSOR [RETURN return_type];

Where cursor_type_name is the name given to cursor type and return_type is the data specification for the data returned. The return type can be any valid data structure relevant for a cursor. This is usually defined using %ROWTYPE attribute or previously defined record TYPE.

The RETURN clause is optional. If RETURN clause is specified it is said to be of strong type of REF CURSOR. If the RETURN clause is missing it is called a weak type. Weak REF CURSOR type cursors are more flexible and can be used with any query, row type structure and can vary within the scope of a single program unit.

In the case of weak REF CURSOR type PL/SQL compiler associates cursor variable to any query. In case of strong REF CURSOR type PL/SQL associates cursor variables to queries that only generate correct set of columns.

The syntax for the declaration of cursor variable is as below.

cursor_name cursor_type_name;

Here cursor_name is the name of the cursor and cursor_type_name is name of the type of cursor previously declared. When the cursor variable is declared the cursor object is not created. Only with the OPEN statement a new cursor object is created and assigned to the variable.

A value needs to be assigned when the cursor variable is opened. The syntax for opening cursor variables is

OPEN cursor_name FOR select_statement;

Here cursor_name is the name of the cursor and select_statement is a SQL SELECT statement.
For strong REF_CURSOR type the structure of select statement specified must be compatible with return data type specified.

The FETCH statement is used to fetch data from a cursor variable. The syntax is

FETCH cursor_name INTO variable_list;

The variable list should be compatible with data type structure specified in query associated with cursor variable for both strong REF_CURSOR type and weak REF_CURSOR types. When the cursor variable is of strong REF_CURSOR type the compiler does the check whereas for weak REF_CURSOR type the check happens at run time.

If there are incompatible record structures PL/SQL will raise the exception ROWTYPE_MISMATCH.

Just like explicit cursor the CLOSE statement is used to close cursor variable.

CLOSE cursor_name;

where cursor_name is the name of the cursor to be closed.

PL/SQL allows assignment operations with cursor variables and also to pass them as arguments to procedures and functions. However in order to do so the cursor variables need to follow set of predefined compile time and runtime rowtype matching rules. If one cursor variable is assigned to another they develop into aliases for the same cursor object. Any action taken on the cursor object through one variable is also obtainable and reflected in the other.

The scope of a cursor variable is similar to that of a static cursor. It is limited to the PL/SQL block in which the variable is declared. If declared in a package it becomes globally accessible. The scope of a cursor object remains as long as one active cursor variable refers to that cursor object. In different terms, a cursor object can be created in one PL/SQL block and assigned to a cursor variable. This can be further assigned to another cursor variable with different scope and remain accessible even if first cursor variable goes out of scope.

While cursor variables are subject to certain restrictions such as they cannot be used with dynamic SQL, cannot be declared in a package, cannot be stored in database columns etc, they have the advantages of letting the programmer associate a cursor variable with different queries at different points in execution, share the result of a cursor variable by passing it as argument to a procedure or function, assign the contents of one cursor variable to another and also allow the usage of the full functionality of static cursors. Thus using cursor variables help to develop performance and to streamline the code.

| Characterization of Materialized views and its types in Oracle10g | Introduction to Capability Maturity Model (CMM) | Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server | Oracle Application Server10g – Business Intelligence Services | Oracle Application Server10g – Content Management Services | Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g | Overview of Flashback Features in Oracle10g | Overview on Database Change Notification in Oracle10g | Overview on Implicit and Explicit Cursors in PL/SQL | Overview on Sequential Control and Conditional Control Statements in PL/SQL | Overview on some of the new features in Oracle Database 10g | Overview on the usage of Cursor Variables in PL/SQL | Performance Tuning in Oracle10g – Best Practices in Application Development | Representing Conditional Expressions as Data in Oracle10g | Understanding Indexes and their key benefits in Oracle10g | Understanding Oracle10g Application Server –Portal Services |