Introduction to Cursor Variables Oracle Tips by Burleson Consulting

The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

A cursor variable is a pointer that distinguishes the current row in a resultset from a multi-row query.  Cursor variables have several advantages over explicit cursors including:

• Cursor variables can point to a variety of queries provided the queries have a suitable return type.  In contrast, explicit cursors are tied to individual queries.

• Cursor variables allow cursors to be opened independently of being processed.

• Cursor variables can be passed as parameters between application layers, as well as between server side components.

• Cursor variables can be used to reduce client-server network traffic by allowing several cursors to be opened on the server in a single round trip.

The following sections illustrate ways in which cursor variables can be used in PL/SQL code.  A thorough understanding of these features will allow programmers to build more efficient PL/SQL APIs and may prove useful as a replacement for passing large collections as parameters between PL/SQL.

The example code in this chapter relies on the cursor_variable_test table which is created with the cursor_variable_test.sql script listed below.

cursor_variable_test.sql

-- Create and populate a test table.
CREATE TABLE cursor_variable_test (
  id           NUMBER(10),
  description  VARCHAR2(50)
);

INSERT INTO cursor_variable_test (id, description) VALUES (1, 'One');
INSERT INTO cursor_variable_test (id, description) VALUES (2, 'Two');
INSERT INTO cursor_variable_test (id, description) VALUES (3, 'Three');
COMMIT;

The examples in this chapter regularly make use of the SYS_REFCURSOR type which became available in Oracle9i.  To make this script backwards compatible, create the package specification listed in the cursor_api.sql script and replace any reference to “SYS_REFCURSOR” with “cursor_api.refcursor”.

cursor_api.sql
-- *****************************************************************

CREATE OR REPLACE PACKAGE cursor_api AS

  TYPE refcursor IS REF CURSOR;

END cursor_ api;
/

How cursor variables are defined is the topic of the next section.

Defining Cursor Variables

Cursor variables are defined using a REF CURSOR type. The type is defined in one of two ways:

• Strongly Typed - The REF CURSOR type is restricted to an individual return type using the RETURN clause.  Although this reduces the chances of runtime errors since column mismatches are detected at compilation time, it also limits the overall flexibility of the type.

• Weakly Typed - The RETURN clause is omitted allowing the type to reference any return type.  This gives greater flexibility, but it increases the likelihood of runtime errors because column mismatches are not picked up at compile time.

The cursor_variable_definitions.sql script shows how strongly and weakly typed cursor variables are defined.

cursor_variable_definitions.sql

SET SERVEROUTPUT ON SIZE 1000000

-- Strongly typed REF CURSOR.
DECLARE
  TYPE t_ref_cursor IS REF CURSOR RETURN cursor_variable_test%ROWTYPE;
  c_cursor  t_ref_cursor;

  l_row     cursor_variable_test%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line('Strongly typed REF CURSOR');

  OPEN c_cursor FOR
    SELECT *
    FROM   cursor_variable_test;

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND;
 
    DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
  END LOOP; 

  CLOSE c_cursor;
END;
/

-- Weakly typed REF CURSOR.
DECLARE
  TYPE t_ref_cursor IS REF CURSOR;
  c_cursor  t_ref_cursor;

  l_row     cursor_variable_test%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line('Weakly typed REF CURSOR');

  OPEN c_cursor FOR
    SELECT *
    FROM   cursor_variable_test;

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND; 

    DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
  END LOOP;

  CLOSE c_cursor;
END;
/

-- Weakly typed REF CURSOR using SYS_RECURSOR.
DECLARE
  c_cursor  SYS_REFCURSOR;

  l_row     cursor_variable_test%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line('Weakly typed REF CURSOR using SYS_RECURSOR');

  OPEN c_cursor FOR
    SELECT *
    FROM   cursor_variable_test;

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND;
 
    DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
  END LOOP;

  CLOSE c_cursor;
END;
/

The cursor_variable_definitions.sql script defines three anonymous PL/SQL blocks. Each block defines cursor variables that are opened and closed for a simple query against the test table.  

The first block provides an example of a strongly typed cursor variable, while the following two examples are examples of weakly typed cursor variables.  The third block uses a cursor variable that is defined using the predefined SYS_REFCURSOR type, negating the need to define a weakly typed REF CURSOR type. 

The output from this script shows these variations all function as expected.

SQL> @cursor_variable_definitions.sql
Strongly typed REF CURSOR
1 : One
2 : Two
3 : Three

PL/SQL procedure successfully completed.

Weakly typed REF CURSOR
1 : One
2 : Two
3 : Three

PL/SQL procedure successfully completed.

Weakly typed REF CURSOR using SYS_RECURSOR
1 : One
2 : Two
3 : Three

PL/SQL procedure successfully completed.

The return value of a strongly typed REF CURSOR must be a record which can be defined using the %ROWTYPE and %TYPE attributes or as a record structure.  The return_types.sql script gives an example of each method along with an invalid scalar definition.

return_types.sql
-- *****************************************************************

-- Strongly typed REF CURSOR using %ROWTYPE.
DECLARE
  TYPE t_ref_cursor IS REF CURSOR RETURN cursor_variable_test%ROWTYPE;
  c_cursor  t_ref_cursor; 

  l_row   cursor_variable_test%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line('Strongly typed REF CURSOR using %ROWTYPE');

  OPEN c_cursor FOR
    SELECT *
    FROM   cursor_variable_test;   

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND;
 
    DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
  END LOOP;

  CLOSE c_cursor;
END;
/

-- Strongly typed REF CURSOR using %TYPE.
DECLARE
  l_row   cursor_variable_test%ROWTYPE;

  TYPE t_ref_cursor IS REF CURSOR RETURN l_row%TYPE;
  c_cursor  t_ref_cursor;
BEGIN
  DBMS_OUTPUT.put_line('Strongly typed REF CURSOR using %TYPE');

  OPEN c_cursor FOR
    SELECT *
    FROM   cursor_variable_test;   

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND; 

    DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
  END LOOP;

  CLOSE c_cursor;
END;
/

-- Strongly typed REF CURSOR using RECORD.
DECLARE
  TYPE t_return_types_rec IS RECORD (
    id           NUMBER(10),
    description  VARCHAR2(50)
  );     

  TYPE t_ref_cursor IS REF CURSOR RETURN t_return_types_rec;
  c_cursor  t_ref_cursor;

  l_row   t_return_types_rec;
BEGIN
  DBMS_OUTPUT.put_line('Strongly typed REF CURSOR using RECORD');

  OPEN c_cursor FOR
    SELECT *
    FROM   cursor_variable_test;   

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND; 

    DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
  END LOOP;

  CLOSE c_cursor;
END;
/

-- Strongly typed REF CURSOR using SCALAR type. Expect an error!
DECLARE
  TYPE t_ref_cursor IS REF CURSOR RETURN NUMBER;
  c_cursor  t_ref_cursor;

  l_row   NUMBER;
BEGIN
  DBMS_OUTPUT.put_line('Strongly typed REF CURSOR using SCALAR type. Expect an error!');

  OPEN c_cursor FOR
    SELECT COUNT(*)
    FROM   cursor_variable_test;   

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND; 

    DBMS_OUTPUT.put_line(l_row);
  END LOOP;

  CLOSE c_cursor;
END;
/

The output from this script is listed below.  Notice that the three variations on the record return type work correctly, while the scalar return type fails as expected.

SQL> @return_types.sql
Strongly typed REF CURSOR using %ROWTYPE
1 : One
2 : Two
3 : Three

PL/SQL procedure successfully completed.

Strongly typed REF CURSOR using %TYPE
1 : One
2 : Two
3 : Three

PL/SQL procedure successfully completed.

Strongly typed REF CURSOR using RECORD
1 : One
2 : Two
3 : Three

PL/SQL procedure successfully completed.

  TYPE t_ref_cursor IS REF CURSOR RETURN NUMBER;
                       *
ERROR at line 2:
ORA-06550: line 2, column 24:
PLS-00362: invalid cursor return type; 'NUMBER' must be a record type
ORA-06550: line 2, column 3:
PL/SQL: Item ignored

The next section shows how cursor variables can be passed as parameters between database procedures and functions.

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006. You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts: