Cursor Variable Declaration

A cursor variable is like an explicit cursor that is not limited to one query.

To create a cursor variable, either declare a variable of the predefined type SYS_REFCURSOR or define a REF CURSOR type and then declare a variable of that type.

Restrictions on Cursor Variables

• You cannot declare a cursor variable in a package specification.

  That is, a package cannot have a public cursor variable (a cursor variable that can be referenced from outside the package).

• You cannot store the value of a cursor variable in a collection or database column.

• You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.

• You can use a cursor variable in a server-to-server remote procedure call (RPC) only if the remote database is not an Oracle Database accessed through a Procedural Gateway.

• You cannot use LOB parameters in a server-to-server RPC.

Topics

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

ref_cursor_type_definition ::=

Description of the illustration ref_cursor_type_definition.gif

cursor_variable_declaration ::=

Description of the illustration cursor_variable_declaration.gif

Semantics

ref_cursor_type_definition

type

Name of the REF CURSOR type that you are defining.

RETURN

Specifies the data type of the value that the cursor variable returns.

Specify RETURN to define a strong REF CURSOR type. Omit RETURN to define a weak REF CURSOR type. For information about strong and weak REF CURSOR types, see "Creating Cursor Variables".

db_table_or_view

Name of a database table or view, which must be accessible when the declaration is elaborated.

cursor

Name of a previously declared explicit cursor.

cursor_variable

Name of a previously declared cursor variable.

record

Name of a user-defined record.

record_type

Name of a user-defined type that was defined with the data type specifier RECORD.

ref_cursor_type

Name of a user-defined type that was defined with the data type specifier REF CURSOR.

cursor_variable_declaration

cursor_variable

Name of the cursor variable that you are declaring.

type

Type of the cursor variable that you are declaring—either SYS_REFCURSOR or the name of the REF CURSOR type that you defined previously.

SYS_REFCURSOR is a weak type. For information about strong and weak REF CURSOR types, see "Creating Cursor Variables".

Examples

• Example 6-24, "Cursor Variable Declarations"

• Example 6-25, "Cursor Variable with User-Defined Return Type"

• Example 6-28, "Variable in Cursor Variable Query—No Result Set Change"

• Example 6-29, "Variable in Cursor Variable Query—Result Set Change"

• Example 6-30, "Procedure to Open Cursor Variable for One Query"

• Example 6-31, "Opening Cursor Variable for Chosen Query (Same Return Type)"

• Example 6-32, "Opening Cursor Variable for Chosen Query (Different Return Types)"

• Example 6-33, "Cursor Variable as Host Variable in Pro*C Client Program"

Related Topics

In this chapter:

• "CLOSE Statement"

• "Named Cursor Attribute"

• "Explicit Cursor Declaration and Definition"

• "FETCH Statement"

• "OPEN FOR Statement"

• "%ROWTYPE Attribute"

• "%TYPE Attribute"

In other chapters:

• "Cursor Variables"

• "Passing CURSOR Expressions to Pipelined Table Functions"