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.
-
Using a cursor variable in a server-to-server remote procedure call (RPC) causes an error. However, you can use a cursor variable in a server-to-server RPC if the remote database is a non-Oracle database accessed through a Procedural Gateway.
-
You cannot use LOB parameters in a server-to-server RPC.
ref_cursor_type_definition ::=
Description of the illustration ref_cursor_type_definition.gif
cursor_variable_declaration ::=
Description of the illustration cursor_variable_declaration.gif
Name of the REF CURSOR type that you are defining.
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".
Name of a database table or view, which must be accessible when the declaration is elaborated.
Name of a previously declared explicit cursor.
Name of a previously declared cursor variable.
Name of a user-defined record.
Name of a user-defined type that was defined with the data type specifier RECORD.
Name of a user-defined type that was defined with the data type specifier REF CURSOR.
Name of the cursor variable that you are declaring.
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".
-
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"