PL/SQL User's Guide and Reference

Using Cursor Variables

Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, cursors differ from cursor variables the way constants differ from variables. Whereas a cursor is static, a cursor variable is dynamic because it is not tied to a specific query. You can open a cursor variable for any type-compatible query. This gives you more flexibility.

Also, you can assign new values to a cursor variable and pass it as a parameter to subprograms, including subprograms stored in an Oracle database. This gives you an easy way to centralize data retrieval.

Cursor variables are available to every PL/SQL client. For example, you can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program, then pass it as a bind variable to PL/SQL. Moreover, application development tools such as Oracle Forms and Oracle Reports, which have a PL/SQL engine, can use cursor variables entirely on the client side.

The Oracle Server also has a PL/SQL engine. So, you can pass cursor variables back and forth between an application and server via remote procedure calls (RPCs).

What Are Cursor Variables?

Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some object instead of the object itself. So, declaring a cursor variable creates a pointer, not an object. In PL/SQL, a pointer has datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. Therefore, a cursor variable has datatype REF CURSOR. Currently, cursor variables are the only REF variables that you can declare.

To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. To access the information, you can use an explicit cursor, which names the work area. Or, you can use a cursor variable, which points to the work area. Whereas a cursor always refers to the same query work area, a cursor variable can refer to different work areas. So, cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected.

Why Use Cursor Variables?

Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. Neither PL/SQL nor any of its clients owns a result set; they simply share a pointer to the query work area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle Server can all refer to the same work area.

A query work area remains accessible as long as any cursor variable points to it. Therefore, you can pass the value of a cursor variable freely from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes.

If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, then continue to fetch from it back on the client side.

Also, you can reduce network traffic by having a PL/SQL block open (or close) several host cursor variables in a single round trip.

Defining REF CURSOR Types

To create cursor variables, you take two steps. First, you define a REF CURSOR type, then declare cursor variables of that type. You can define REF CURSOR types in any PL/SQL block, subprogram, or package using the syntax

TYPE ref_type_name IS REF CURSOR RETURN return_type;

where ref_type_name is a type specifier used in subsequent declarations of cursor variables and return_type must represent a record or a row in a database table. In the following example, you specify a return type that represents a row in the database table dept:

DECLARE
   TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;

REF CURSOR types can be strong (restrictive) or weak (nonrestrictive). As the next example shows, a strong REF CURSOR type definition specifies a return type, but a weak definition does not:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;  -- strong
   TYPE GenericCurTyp IS REF CURSOR;  -- weak

Strong REF CURSOR types are less error prone because the PL/SQL compiler lets you associate a strongly typed cursor variable only with type-compatible queries. However, weak REF CURSOR types are more flexible because the compiler lets you associate a weakly typed cursor variable with any query.

Declaring Cursor Variables

Once you define a REF CURSOR type, you can declare cursor variables of that type in any PL/SQL block or subprogram. In the following example, you declare the cursor variable dept_cv:

DECLARE
   TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
   dept_cv DeptCurTyp;  -- declare cursor variable

Note: You cannot declare cursor variables in a package. Unlike package variables, cursor variables do not have persistent state. Remember, declaring a cursor variable creates a pointer, not an object. So, cursor variables cannot be saved in the database.

Cursor variables follow the usual scoping and instantiation rules. Local PL/SQL cursor variables are instantiated when you enter a block or subprogram and cease to exist when you exit.

In the RETURN clause of a REF CURSOR type definition, you can use %ROWTYPE to specify a record type that represents a row returned by a (previously declared) cursor variable, as follows:

DECLARE
   TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   tmp_cv TmpCurTyp;  -- declare cursor variable
   TYPE EmpCurTyp IS REF CURSOR RETURN tmp_cv%ROWTYPE;
   emp_cv EmpCurTyp;  -- declare cursor variable

Likewise, you can use %TYPE to provide the datatype of a record variable, as the following example shows:

DECLARE
   dept_rec dept%ROWTYPE;  -- declare record variable
   TYPE DeptCurTyp IS REF CURSOR RETURN dept_rec%TYPE;
   dept_cv DeptCurTyp;  -- declare cursor variable

In the final example, you specify a user-defined RECORD type in the RETURN clause:

DECLARE
   TYPE EmpRecTyp IS RECORD (
      empno NUMBER(4),
      ename VARCHAR2(1O),
      sal   NUMBER(7,2));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
   emp_cv EmpCurTyp;  -- declare cursor variable

As Parameters

You can declare cursor variables as the formal parameters of functions and procedures. In the following example, you define the REF CURSOR type EmpCurTyp, then declare a cursor variable of that type as the formal parameter of a procedure:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS ...

Controlling Cursor Variables

You use three statements to control a cursor variable: OPEN-FOR, FETCH, and CLOSE. First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result set one at a time. When all the rows are processed, you CLOSE the cursor variable.

Opening a Cursor Variable

The OPEN-FOR statement associates a cursor variable with a multi-row query, executes the query, and identifies the result set. The statement syntax is

OPEN {cursor_variable_name | :host_cursor_variable_name} 
   FOR select_statement;

where host_cursor_variable_name identifies a cursor variable declared in a PL/SQL host environment such as an OCI or Pro*C program.

Unlike cursors, cursor variables do not take parameters. No flexibility is lost, however, because you can pass whole queries (not just parameters) to a cursor variable. The query can reference bind variables and PL/SQL variables, parameters, and functions but cannot be FOR UPDATE.

In the example below, you open the cursor variable emp_cv. Notice that you can apply cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) to a cursor variable.

IF NOT emp_cv%ISOPEN THEN 
   /* Open cursor variable. */
   OPEN emp_cv FOR SELECT * FROM emp;
END IF;

Other OPEN-FOR statements can open the same cursor variable for different queries. You need not close a cursor variable before reopening it. (Recall that consecutive OPENs of a static cursor raise the predefined exception CURSOR_ALREADY_OPEN.) When you reopen a cursor variable for a different query, the previous query is lost.

In a Stored Procedure Typically, you open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters. For example, the following packaged procedure opens the cursor variable emp_cv:

CREATE PACKAGE emp_data AS
   ...
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp);
END emp_data;
 
CREATE PACKAGE BODY emp_data AS
   ...
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
   BEGIN
      OPEN emp_cv FOR SELECT * FROM emp;
   END open_emp_cv;
END emp_data;

When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the IN OUT mode. That way, the subprogram can pass an open cursor back to the caller.

Alternatively, you can use a standalone procedure to open the cursor variable. Simply define the REF CURSOR type in a separate package, then reference that type in the standalone procedure. For instance, if you create the following (bodiless) package, you can create standalone procedures that reference the types it defines:

CREATE PACKAGE cv_types AS
   TYPE GenericCurTyp IS REF CURSOR;
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   TYPE DeptCurTyp IS REF CURSOR RETURN dept%ROWTYPE;
   ...
END cv_types;

In the following example, you create a standalone procedure that references the REF CURSOR type EmpCurTyp, which is defined in the package cv_types:

CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.EmpCurTyp) AS BEGIN 
   OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;

To centralize data retrieval, you can group type-compatible queries in a stored procedure. In the example below, the packaged procedure declares a selector as one of its formal parameters. (In this context, a selector is a variable used to select one of several alternatives in a conditional control statement.) When called, the procedure opens the cursor variable emp_cv for the chosen query.

CREATE PACKAGE emp_data AS
   TYPE GenericCurTyp IS REF CURSOR;
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
                          choice IN NUMBER);
END emp_data;
 
CREATE PACKAGE BODY emp_data AS
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
                          choice IN NUMBER) IS
   BEGIN
      IF choice = 1 THEN
         OPEN emp_cv FOR SELECT * FROM emp WHERE comm IS NOT NULL;
      ELSIF choice = 2 THEN
         OPEN emp_cv FOR SELECT * FROM emp WHERE sal > 2500;
      ELSIF choice = 3 THEN
         OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = 20;
      END IF;
   END open_emp_cv;
END emp_data;

For more flexibility, you can pass a cursor variable and selector to a stored procedure that executes queries with different return types. Consider the following example:

CREATE PACKAGE BODY emp_data AS
   PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,
                      choice     IN NUMBER) IS
   BEGIN
      IF choice = 1 THEN
         OPEN generic_cv FOR SELECT * FROM emp;
      ELSIF choice = 2 THEN
         OPEN generic_cv FOR SELECT * FROM dept;
      ELSIF choice = 3 THEN
         OPEN generic_cv FOR SELECT * FROM salgrade;
      END IF;
   END open_cv;
END emp_data;

Using a Bind Variable You can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To use the cursor variable, you must pass it as a bind variable to PL/SQL. In the following Pro*C example, you pass a host cursor variable and selector to a PL/SQL block, which opens the cursor variable for the chosen query:

EXEC SQL BEGIN DECLARE SECTION;
   ...
   /* Declare host cursor variable. */
   SQL_CURSOR generic_cv;
   int        choice;
EXEC SQL END DECLARE SECTION;
...
/* Initialize host cursor variable. */
EXEC SQL ALLOCATE :generic_cv;
...
/* Pass host cursor variable and selector to PL/SQL block. */
EXEC SQL EXECUTE
BEGIN
   IF :choice = 1 THEN
      OPEN :generic_cv FOR SELECT * FROM emp;
   ELSIF :choice = 2 THEN
      OPEN :generic_cv FOR SELECT * FROM dept;
   ELSIF :choice = 3 THEN
      OPEN :generic_cv FOR SELECT * FROM salgrade;
   END IF;
END;
END-EXEC;

Host cursor variables are compatible with any query return type. They behave just like weakly typed PL/SQL cursor variables.

Fetching from a Cursor Variable

The FETCH statement retrieves rows one at a time from the result set of a multi-row query. The statement syntax follows:

FETCH {cursor_variable_name | :host_cursor_variable_name}
   INTO {variable_name[, variable_name]... | record_name};

In the next example, you fetch rows from the cursor variable emp_cv into the user-defined record emp_rec:

LOOP
   /* Fetch from cursor variable. */
   FETCH emp_cv INTO emp_rec;
   EXIT WHEN emp_cv%NOTFOUND; -- exit when last row is fetched
   -- process data record
END LOOP;

Any variables in the associated query are evaluated only when the cursor variable is opened. To change the result set or the values of variables in the query, you must reopen the cursor variable with the variables set to their new values. However, you can use a different INTO clause on separate fetches with the same cursor variable. Each fetch retrieves another row from the same result set.

PL/SQL makes sure the return type of the cursor variable is compatible with the INTO clause of the FETCH statement. For each column value returned by the query associated with the cursor variable, there must be a corresponding, type-compatible field or variable in the INTO clause. Also, the number of fields or variables must equal the number of column values. Otherwise, you get an error.

The error occurs at compile time if the cursor variable is strongly typed or at run time if it is weakly typed. At run time, PL/SQL raises the predefined exception ROWTYPE_ MISMATCH before the first fetch. So, if you trap the error and execute the FETCH statement using a different INTO clause, no rows are lost.

When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN (or IN OUT) mode. However, if the subprogram also opens the cursor variable, you must specify the IN OUT mode.

If you try to fetch from a closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.

Closing a Cursor Variable

The CLOSE statement disables a cursor variable. After that, the associated result set is undefined. The statement syntax follows:

CLOSE {cursor_variable_name | :host_cursor_variable_name);

In the following example, when the last row is processed, you close the cursor variable emp_cv:

LOOP
   FETCH emp_cv INTO emp_rec;
   EXIT WHEN emp_cv%NOTFOUND;
   -- process data record
END LOOP;
/* Close cursor variable. */
CLOSE emp_cv;

When declaring a cursor variable as the formal parameter of a subprogram that closes the cursor variable, you must specify the IN (or IN OUT) mode.

If you try to close an already-closed or never-opened cursor variable, PL/SQL raises the predefined exception INVALID_CURSOR.

Some Examples

Consider the stored procedure below, which searches the database of a main library for books, periodicals, and tapes. A master table stores the title and category code (1 = book, 2 = periodical, 3 = tape) of each item. Three detail tables store category-specific information. When called, the procedure searches the master table by title, uses the associated category code to pick an OPEN-FOR statement, then opens a cursor variable for a query of the proper detail table.

CREATE PACKAGE cv_types AS
   TYPE LibCurTyp IS REF CURSOR;
   ...
END cv_types;

CREATE PROCEDURE find_item (title  VARCHAR2(100),
                            lib_cv IN OUT cv_types.LibCurTyp) AS
   code BINARY_INTEGER;
BEGIN
   SELECT item_code FROM titles INTO code
      WHERE item_title = title;
   IF code = 1 THEN 
      OPEN lib_cv FOR SELECT * FROM books 
         WHERE book_title = title;
   ELSIF code = 2 THEN 
      OPEN lib_cv FOR SELECT * FROM periodicals 
         WHERE periodical_title = title;
   ELSIF code = 3 THEN 
      OPEN lib_cv FOR SELECT * FROM tapes 
         WHERE tape_title = title;
   END IF;
END find_item;

A client-side application in a branch library might use the following PL/SQL block to display the retrieved information:

DECLARE
   lib_cv         cv_types.LibCurTyp;
   book_rec       books%ROWTYPE;
   periodical_rec periodicals%ROWTYPE;
   tape_rec       tapes%ROWTYPE;
BEGIN
   get_title(:title);  -- title is a host variable
   find_item(:title, lib_cv);
   FETCH lib_cv INTO book_rec;
   display_book(book_rec);

EXCEPTION
   WHEN ROWTYPE_MISMATCH THEN
      BEGIN
         FETCH lib_cv INTO periodical_rec;
         display_periodical(periodical_rec);
      EXCEPTION
         WHEN ROWTYPE_MISMATCH THEN
            FETCH lib_cv INTO tape_rec;
            display_tape(tape_rec);
      END;
END;

The following Pro*C program prompts the user to select a database table, opens a cursor variable for a query of that table, then fetches rows returned by the query:

#include <stdio.h>
#include <sqlca.h>
void sql_error();
main()
{
   char temp[32];
   EXEC SQL BEGIN DECLARE SECTION;
      char * uid = "scott/tiger";
      SQL_CURSOR generic_cv;   /* cursor variable */
      int    table_num;        /* selector        */
      struct                   /* EMP record      */
      {
         int   emp_num;
         char  emp_name[11];
         char  job_title[10];
         int   manager;
         char  hire_date[10];
         float salary;
         float commission;
         int   dept_num;
      } emp_rec;
      struct                   /* DEPT record     */
      {         
         int   dept_num;
         char  dept_name[15];
         char  location[14];
      } dept_rec;
      struct                   /* BONUS record    */
      {
         char  emp_name[11];
         char  job_title[10];
         float salary;
      } bonus_rec;
   EXEC SQL END DECLARE SECTION;

   /* Handle Oracle errors. */
   EXEC SQL WHENEVER SQLERROR DO sql_error();

   /* Connect to Oracle. */
   EXEC SQL CONNECT :uid;
 
  /* Initialize cursor variable. */
   EXEC SQL ALLOCATE :generic_cv;

   /* Exit loop when done fetching. */
   EXEC SQL WHENEVER NOT FOUND DO break;

   for (;;)
   {
      printf("\n1 = EMP, 2 = DEPT, 3 = BONUS");
      printf("\nEnter table number (0 to quit): ");
      gets(temp);
      table_num = atoi(temp);
      if (table_num <= 0) break;

      /* Open cursor variable. */
      EXEC SQL EXECUTE
         BEGIN
            IF :table_num = 1 THEN
               OPEN :generic_cv FOR SELECT * FROM emp; 
            ELSIF :table_num = 2 THEN
               OPEN :generic_cv FOR SELECT * FROM dept;
            ELSIF :table_num = 3 THEN
               OPEN :generic_cv FOR SELECT * FROM bonus; 
            END IF;
         END;
      END-EXEC;

      for (;;)
      {
         switch (table_num)
         {
            case 1:  /* Fetch row into EMP record. */
               EXEC SQL FETCH :generic_cv INTO :emp_rec;
               break;
            case 2:  /* Fetch row into DEPT record. */
               EXEC SQL FETCH :generic_cv INTO :dept_rec;
               break;
            case 3:  /* Fetch row into BONUS record. */
               EXEC SQL FETCH :generic_cv INTO :bonus_rec;
               break;
         }
         /* Process data record here. */
      }

      /* Close cursor variable. */
      EXEC SQL CLOSE :generic_cv;
   }
   exit(0);
}
void sql_error()
{  
   /* Handle SQL error here. */
}

Reducing Network Traffic

When passing host cursor variables to PL/SQL, you can reduce network traffic by grouping OPEN-FOR statements. For example, the following PL/SQL block opens five cursor variables in a single round-trip:

/* anonymous PL/SQL block in host environment */ 
BEGIN 
   OPEN :emp_cv FOR SELECT * FROM emp; 
   OPEN :dept_cv FOR SELECT * FROM dept; 
   OPEN :grade_cv FOR SELECT * FROM salgrade; 
   OPEN :pay_cv FOR SELECT * FROM payroll; 
   OPEN :ins_cv FOR SELECT * FROM insurance; 
END;

This might be useful in Oracle Forms, for instance, when you want to populate a multi-block form.

When you pass host cursor variables to a PL/SQL block for opening, the query work areas to which they point remain accessible after the block completes. That allows your OCI or Pro*C program to use these work areas for ordinary cursor operations. In the following example, you open five such work areas in a single round trip:

BEGIN
   OPEN :c1 FOR SELECT 1 FROM dual;
   OPEN :c2 FOR SELECT 1 FROM dual;
   OPEN :c3 FOR SELECT 1 FROM dual;
   OPEN :c4 FOR SELECT 1 FROM dual;
   OPEN :c5 FOR SELECT 1 FROM dual;
END;

The cursors assigned to c1, c2, c3, c4, and c5 behave normally, and you can use them for any purpose. When finished, simply release the cursors, as follows:

BEGIN 
   CLOSE :c1;
   CLOSE :c2;
   CLOSE :c3;
   CLOSE :c4;
   CLOSE :c5;
END;

Avoiding Exceptions

If both cursor variables involved in an assignment are strongly typed, they must have the same datatype. In the following example, even though the cursor variables have the same return type, the assignment raises an exception because they have different datatypes:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   TYPE TmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   ...
   PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
                          tmp_cv IN OUT TmpCurTyp) IS 
   BEGIN 
      ...
      emp_cv := tmp_cv;  -- causes 'wrong type' error
   END;

However, if one or both cursor variables are weakly typed, they need not have the same datatype.

If you try to fetch from, close, or apply cursor attributes to a cursor variable that does not point to a query work area, PL/SQL raises the predefined exception INVALID_CURSOR. You can make a cursor variable (or parameter) point to a query work area in two ways:

OPEN the cursor variable FOR the query.

Assign to the cursor variable the value of an already OPENed host cursor variable or PL/SQL cursor variable.

The following example shows how these ways interact:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   emp_cv1 EmpCurTyp;
   emp_cv2 EmpCurTyp;
   emp_rec emp%ROWTYPE;
BEGIN
   /* The following assignment is useless because emp_cv1
      does not point to a query work area yet. */
   emp_cv2 := emp_cv1;  -- useless 
   /* Make emp_cv1 point to a query work area. */
   OPEN emp_cv1 FOR SELECT * FROM emp;
   /* Use emp_cv1 to fetch first row from emp table. */
   FETCH emp_cv1 INTO emp_rec; 
   /* The following fetch raises an exception because emp_cv2
      does not point to a query work area yet. */
   FETCH emp_cv2 INTO emp_rec;  -- raises INVALID_CURSOR

EXCEPTION
   WHEN INVALID_CURSOR THEN
      /* Make emp_cv1 and emp_cv2 point to same work area. */
      emp_cv2 := emp_cv1;
      /* Use emp_cv2 to fetch second row from emp table. */
      FETCH emp_cv2 INTO emp_rec; 
      /* Reuse work area for another query. */
      OPEN emp_cv2 FOR SELECT * FROM old_emp;
      /* Use emp_cv1 to fetch first row from old_emp table.
         The following fetch succeeds because emp_cv1 and
         emp_cv2 point to the same query work area. */
      FETCH emp_cv1 INTO emp_rec;  -- succeeds
END;

Be careful when passing cursor variables as parameters. At run time, PL/SQL raises ROWTYPE_MISMATCH if the return types of the actual and formal parameters are incompatible.

In the Pro*C example below, you define a packaged REF CURSOR type, specifying the return type emp%ROWTYPE. Next, you create a standalone procedure that references the new type. Then, inside a PL/SQL block, you open a host cursor variable for a query of the dept table. Later, when you pass the open host cursor variable to the stored procedure, PL/SQL raises ROWTYPE_MISMATCH because the return types of the actual and formal parameters are incompatible.

/* bodiless package */
CREATE PACKAGE cv_types AS
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
   ...
END cv_types;

/* standalone procedure */
CREATE PROCEDURE open_emp_cv (emp_cv IN OUT cv_types.EmpCurTyp) AS
BEGIN
   OPEN emp_cv FOR SELECT * FROM emp;
END open_emp_cv;

/* anonymous PL/SQL block in Pro*C program */
EXEC SQL EXECUTE
   BEGIN
      OPEN :cv FOR SELECT * FROM dept;
      ...
      open_emp_cv(:cv); -- raises ROWTYPE_MISMATCH because emp and
                        -- dept tables have different rowtypes
   END;
END-EXEC;

Guarding Against Aliasing

Like all pointers, cursor variables introduce the possibility of aliasing. Consider the example below. After the assignment, emp_cv2 is an alias of emp_cv1 because both point to the same query work area. So, both can alter its state. That is why the first fetch from emp_cv2 fetches the third row (not the first) and why the second fetch from emp_cv2 fails after you close emp_cv1.

PROCEDURE get_emp_data (emp_cv1 IN OUT EmpCurTyp,
                        emp_cv2 IN OUT EmpCurTyp) IS
   emp_rec emp%ROWTYPE;
BEGIN 
   OPEN emp_cv1 FOR SELECT * FROM emp; 
   emp_cv2 := emp_cv1;
   FETCH emp_cv1 INTO emp_rec;  -- fetches first row
   FETCH emp_cv1 INTO emp_rec;  -- fetches second row
   FETCH emp_cv2 INTO emp_rec;  -- fetches third row
   CLOSE emp_cv1;
   FETCH emp_cv2 INTO emp_rec;  -- raises INVALID_CURSOR
   ...
END get_emp_data;

Aliasing also occurs when the same actual parameter appears twice in a subprogram call. Unless both formal parameters are IN parameters, the result is indeterminate, as the following example shows:

DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; 
   emp_cv  EmpCurTyp;
   emp_rec emp%ROWTYPE;
   PROCEDURE open_emp_cv (cv1 IN OUT EmpCurTyp,
                          cv2 IN OUT EmpCurTyp) IS
   BEGIN
      OPEN cv1 FOR SELECT * FROM emp WHERE ename = 'KING';
      OPEN cv2 FOR SELECT * FROM emp WHERE ename = 'BLACK';
   END open_emp_cv;
BEGIN
   open_emp_cv(emp_cv, emp_cv);
   FETCH emp_cv INTO emp_rec;  -- indeterminate; might return 
                               -- row for 'KING' or 'BLACK'
   ...
END;

Restrictions

Currently, cursor variables are subject to the following restrictions, some of which future releases of PL/SQL will remove:

You cannot declare cursor variables in a package because they do not have persistent state.

Remote subprograms on another server cannot accept the values of cursor variables. Therefore, you cannot use RPCs to pass cursor variables from one server to another.

If you pass a host cursor variable (bind variable) to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call.

The query associated with a cursor variable in an OPEN-FOR statement cannot be FOR UPDATE.

You cannot use comparison operators to test cursor variables for equality, inequality, or nullity. For example, the following IF conditions are illegal:

      PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp,
                             tmp_cv IN OUT TmpCurTyp) IS 
      BEGIN
         ...
         IF emp_cv = tmp_cv THEN ...  -- illegal
         IF emp_cv IS NULL THEN ...  -- illegal
      END;

You cannot assign nulls to a cursor variable. For example, the following assignment statement is illegal:

      PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS 
      BEGIN 
         emp_cv := NULL;  -- illegal
         ...
      END;

You cannot use REF CURSOR types to specify column types in a CREATE TABLE or CREATE VIEW statement. So, database columns cannot store the values of cursor variables.

You cannot use a REF CURSOR type to specify the element type of a PL/SQL table, which means that elements in a PL/SQL table cannot store the values of cursor variables. For instance, the following TABLE type definition is illegal:

      DECLARE
         TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
         TYPE EmpCurTabTyp IS TABLE OF EmpCurTyp  -- illegal
            INDEX BY BINARY_INTEGER;

Cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected. For example, the following cursor FOR loop is illegal:

      DECLARE
         CURSOR emp_cur IS SELECT * FROM emp;  -- static cursor
         TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;
         emp_cv  EmpCurTyp;  -- cursor variable
      BEGIN 
         ...
         FOR emp_rec IN emp_cv LOOP ...  -- illegal
            ...
         END LOOP;
      END;

You cannot use cursor variables with dynamic SQL.