14.6.7 Rules for Cursor Variables

This section examines in more detail the rules and issues regarding the use of cursor variables in your programs. These include rowtype matching rules, cursor variable aliases, and scoping issues.

Remember that the cursor variable is a reference to a cursor object or query in the database. It is not the object itself. A cursor variable is said to refer to a given query if either of the following is true:

• An OPEN statement FOR that query was executed with the cursor variable.

• A cursor variable was assigned a value from another cursor variable that refers to that query.

You can perform assignment operations with cursor variables and also pass these variables as arguments to procedures and functions. In order to perform such actions between cursor variables (and to bind a cursor variable to a parameter), the different cursor variables must follow a set of compile-time and runtime rowtype matching rules.

14.6.7.1 Compile-time rowtype matching rules

These are the rules that PL/SQL follows at compile time:

• Two cursor variables (including procedure parameters) are compatible for assignments and argument passing if any of the following is true:

  • Both variables (or parameters) are of a strongREF CURSOR type with the same rowtype_name.

  • Both variables (or parameters) are of a weak REF CURSOR type, regardless of the rowtype_name.

  • One variable (or parameter) is of any strong REF CURSOR type, and the other is of any weak REF CURSOR type.

• A cursor variable (or parameter) of a strong REF CURSOR type may be OPEN FOR a query that returns a rowtype that is structurally equal to the rowtype_ name in the original type declaration.

• A cursor variable (or parameter) of a weak REF CURSOR type may be OPEN FOR any query. The FETCH from such a variable is allowed INTO any list of variables or record structure.

If either of the cursor variables is of the weak REF CURSOR type, then the PL/SQL compiler cannot really validate whether the two different cursor variables will be compatible. That will happen at runtime; the rules are covered in the next section.

14.6.7.2 Runtime rowtype matching rules

These are the rules that PL/SQL follows at runtime:

• A cursor variable (or parameter) of a weak REF CURSOR type may be made to refer to a query of any rowtype regardless of the query or cursor object to which it may have referred earlier.

• A cursor variable (or parameter) of a strong REF CURSOR type may be made to refer only to a query that matches structurally the rowtype_name of the RETURN clause of the REF CURSOR type declaration.

• Two records (or lists of variables) are considered structurally matching with implicit conversions if both of the following are true:

  • The number of fields is the same in both records (or lists).

  • For each field in one record (or variable in one list), a corresponding field in the second list (or a variable in the second list) has the same PL/SQL datatype, or one that can be converted implicitly by PL/SQL to match the first.

• For a cursor variable (or parameter) used in a FETCH statement, the query associated with the cursor variable must structurally match (with implicit conversions) the record or list of variables of the INTO clause of the FETCH statement. This same rule is used for static cursors.