Cursor Variables

A cursor is a static object; a cursor variable is a pointer to a cursor. Because cursor variables are pointers, they can be passed and returned as parameters to subprograms. A cursor variable can also refer to different cursors in its lifetime.

Additional advantages of cursor variables include:

Encapsulation

Queries are centralized in the stored subprogram that opens the cursor variable.
Easy maintenance

If you must change the cursor, then you only make the change in the stored subprogram, not in each application.
Convenient security

The user of the application is the user name used when the application connects to the server. The user must have EXECUTE permission on the stored subprogram that opens the cursor. But, the user need not have READ permission on the tables used in the query. This capability can be used to limit access to the columns in the table and access to other stored subprograms.

Declaring and Opening Cursor Variables

Memory is usually allocated for a cursor variable in the client application using the appropriate ALLOCATE statement. In Pro*C, use the EXEC SQL ALLOCATE cursor_name statement. In OCI, use the Cursor Data Area.

You can also use cursor variables in applications that run entirely in a single server session. You can declare cursor variables in PL/SQL subprograms, open them, and use them as parameters for other PL/SQL subprograms.

Examples of Cursor Variables

This section has examples of cursor variable usage in PL/SQL.

See Also:

For additional cursor variable examples that use programmatic interfaces:

Pro*C/C++ Programmer's Guide
Pro*COBOL Programmer's Guide
Oracle Call Interface Programmer's Guide

Example: Fetching Data with Cursor Variable creates a package that defines a PL/SQL cursor variable type and two procedures, and then invokes the procedures from a PL/SQL block. The first procedure opens a cursor variable using a bind variable in the WHERE clause. The second procedure uses a cursor variable to fetch rows from the EMPLOYEES table.

Fetching Data with Cursor Variable

CREATE OR REPLACE PACKAGE emp_data AS
  TYPE emp_val_cv_type IS REF CURSOR
  RETURN EMPLOYEES%ROWTYPE;
  
  PROCEDURE open_emp_cv (
    emp_cv       IN OUT emp_val_cv_type,
    dept_number  IN     EMPLOYEES.DEPARTMENT_ID%TYPE
  );
 
 PROCEDURE fetch_emp_data (
   emp_cv   IN  emp_val_cv_type,
   emp_row  OUT EMPLOYEES%ROWTYPE
 );
END emp_data;
/
CREATE OR REPLACE PACKAGE BODY emp_data AS
  PROCEDURE open_emp_cv (
    emp_cv       IN OUT emp_val_cv_type,
    dept_number  IN     EMPLOYEES.DEPARTMENT_ID%TYPE
  )
  IS
  BEGIN
    OPEN emp_cv FOR
    SELECT * FROM EMPLOYEES
    WHERE DEPARTMENT_ID = dept_number;
  END open_emp_cv;
  
  PROCEDURE fetch_emp_data (
    emp_cv   IN  emp_val_cv_type,
    emp_row  OUT EMPLOYEES%ROWTYPE
  )
  IS
  BEGIN
    FETCH emp_cv INTO emp_row;
  END fetch_emp_data;
END emp_data;
/

Invoke packaged procedures:

DECLARE
  emp_curs     emp_data.emp_val_cv_type;
  dept_number  EMPLOYEES.DEPARTMENT_ID%TYPE;
  emp_row      EMPLOYEES%ROWTYPE;
  
BEGIN
  dept_number := 20;
  
  -- Open cursor, using variable:
 
 emp_data.open_emp_cv(emp_curs, dept_number);
 
 -- Fetch and display data:
 
 LOOP
   emp_data.fetch_emp_data(emp_curs, emp_row);
   EXIT WHEN emp_curs%NOTFOUND;
   DBMS_OUTPUT.PUT(emp_row.LAST_NAME || '  ');
   DBMS_OUTPUT.PUT_LINE(emp_row.SALARY);
 END LOOP;
END;
/

In Example: Cursor Variable with Discriminator, the procedure opens a cursor variable for either the EMPLOYEES table or the DEPARTMENTS table, depending on the value of the parameter discrim. The anonymous block invokes the procedure to open the cursor variable for the EMPLOYEES table, but fetches from the DEPARTMENTS table, which raises the predefined exception ROWTYPE_MISMATCH.

Cursor Variable with Discriminator

CREATE OR REPLACE PACKAGE emp_dept_data AS
  TYPE cv_type IS REF CURSOR;
  
  PROCEDURE open_cv (
    cv       IN OUT cv_type,
    discrim  IN     POSITIVE
  );
  END emp_dept_data;
/
 
CREATE OR REPLACE PACKAGE BODY emp_dept_data AS
  PROCEDURE open_cv (
    cv      IN OUT cv_type,
    discrim IN     POSITIVE) IS
  BEGIN
    IF discrim = 1 THEN
    OPEN cv FOR
      SELECT * FROM EMPLOYEES;
    ELSIF discrim = 2 THEN
      OPEN cv FOR
        SELECT * FROM DEPARTMENTS;
    END IF;
  END open_cv;
END emp_dept_data;
/

Invoke procedure open_cv from anonymous block:

DECLARE
  emp_rec   EMPLOYEES%ROWTYPE;
  dept_rec  DEPARTMENTS%ROWTYPE;
  cv        Emp_dept_data.CV_TYPE;
BEGIN
  emp_dept_data.open_cv(cv, 1);  -- Open cv for EMPLOYEES fetch.
  FETCH cv INTO dept_rec;        -- Fetch from DEPARTMENTS.
  DBMS_OUTPUT.PUT(dept_rec.DEPARTMENT_ID);
  DBMS_OUTPUT.PUT_LINE('  ' || dept_rec.LOCATION_ID);
EXCEPTION
  WHEN ROWTYPE_MISMATCH THEN
     BEGIN
       DBMS_OUTPUT.PUT_LINE
         ('Row type mismatch, fetching EMPLOYEES data ...');
       FETCH cv INTO emp_rec;
       DBMS_OUTPUT.PUT(emp_rec.DEPARTMENT_ID);
       DBMS_OUTPUT.PUT_LINE('  ' || emp_rec.LAST_NAME);
     END;
END;
/

Result:

Row type mismatch, fetching EMPLOYEES data ...
90  King