MacLochlainns Weblog

A week or two ago, I noticed a discussion on the Oracle Technical Network forum that asked some questions about reference cursors. The discussion made me reflect if there were a couple simple rules for using reference cursors. This contains my take on the rules and some examples.

What are they …
There are two types of reference cursors. They are either weakly or strongly typed PL/SQL only data types. A weakly typed cursor doesn’t anchor the output of a query to a table or view, and implements the PL/SQL SYS_REFCURSOR type. A strongly typed cursor anchors the output of a query to a table or view, and typically anchors itself to a package or local user defined type (UDT) of a cursor.

They’re defined in the declaration block or a package specification, like this:

TYPE weak_cursor IS REF CURSOR;
TYPE strong_cursor IS REF CURSOR RETURN table_name%ROWTYPE;

When you opt to use a weakly typed cursor, you can anchor it to a PL/SQL structure that is a collection of a PL/SQL record structure. This is known as an associative array, and commonly called a PL/SQL table by folks who pre-date the Oraclei documentation that Oracle would prefer we forget. However, it seems politically insensitive to use that language even though Oracle published it as such in the Oracle 8 documentation because it isn’t really a table but an implementation of a list of record structures. That means the index is sparsely populated or may contain breaks in a numeric index sequence. You can also use character string indexes from Oracle 10g forward to index this type of collection.

Why should you use them …
They’re useful structures when you want to open a cursor with known output columns in one program unit and move it to another for processing. Strongly typed reference cursors are appropriate for this purpose when both the passing and calling program units are in PL/SQL. Weakly typed reference cursors can also be used passing and calling PL/SQL programs. Weakly typed reference cursors require the same structures as strong reference cursors. Specifically, you will need a record structure for row-by-row fetch and collection of a record structure for bulk fetch. Whether you define them as strong or weak, you’ll need these structures for use inside PL/SQL program units that read the cursor. As a rule of thumb, you should generally define them as strongly typed reference cursors when they’re used exclusively in PL/SQL.

The most effective use of weakly typed reference cursors is in external OCI programming languages. Actually, it appears that weakly typed cursors have little use anywhere but in an OCI program. In external programming languages, you can fetch associations and dynamically discover the structure of a weakly typed reference cursor. That’s why they’re useful in OCI programming languages. You can find an example of using a weakly typed cursor in a PHP program on this blog entry.

How you use them …
You can find an example of using a weakly typed reference cursor as an input parameter in this discussion of pipelined PL/SQL functions. You can return a weakly typed cursor from an NDS (Native Dynamic SQL) statement as follows:

-- Creates a function to dynamically open a cursor and return it as a return type.
CREATE OR REPLACE FUNCTION weakly_typed_cursor
(title_segment VARCHAR2) RETURN SYS_REFCURSOR IS
  weak_cursor SYS_REFCURSOR;
  stmt VARCHAR2(4000);
BEGIN
  -- Create dynamic SQL statement.
  stmt := 'SELECT item_title, item_subtitle '
       || 'FROM   item '
       || 'WHERE  REGEXP_LIKE(item_title,:input)';
  -- Explicit cursor structures are required for system reference cursors.
  OPEN weak_cursor FOR stmt USING title_segment;
  RETURN weak_cursor;
 
END;
/

Outside of an OCI program, the only explicit way to query this is with a SQL statement like:

SQL> VARIABLE cursor REFCURSOR
SQL> CALL weakly_typed_cursor('Harry') INTO :cursor
SQL> SELECT :cursor FROM dual;

Alternatively, you can do the following implicit call:

SQL> SELECT weakly_typed_cursor('Harry') FROM dual;

If you tried to select it using the TABLE function, you’d get the following error:

SQL> SELECT * FROM TABLE(weakly_typed_cursor);
     *
ERROR at line 2:
ORA-22905: cannot access ROWS FROM a non-nested TABLE item

You can return and query a weakly typed cursor as a strongly typed cursor by doing four things. Anchor the cursor statement to a table or view in the database catalog. Create a record structure to store rows of the reference cursor in an ordinary fetch, or a record structure and associative array collection data type to store a set of rows of the reference cursor. Then, explicitly open the reference cursor into the variable. Here is the detail to support those steps:

1. Create a package specification, that acts like an Object-oriented interface because all it contains are type definitions that you’ll implement in other code modules (provided they have the EXECUTE privilege on the package):

-- Create a structure declaration package, like an interface or abstract class.
CREATE OR REPLACE PACKAGE pipeliner IS
  -- Declares a row structure that doesn't map to a catalog object.
  TYPE title_structure IS RECORD
  (item_title    VARCHAR2(60)
  ,item_subtitle VARCHAR2(60));
  -- Declares an associative array, or a collection of a PL/SQL row structure.
  TYPE title_collection IS TABLE OF title_structure;
END pipeliner;
/

2. You use the WEAKLY_TYPED_CURSOR function presented earlier in this blog to create a weakly typed cursor as the return type of a stored function.

3. The last step creates a function that translates a weakly typed reference cursor into an aggregate table by using a pipelined function, as shown in the illustration.

-- A function that receives a cursor as an input and returns an aggregate table.
CREATE OR REPLACE FUNCTION use_of_input_cursor
(incoming_cursor SYS_REFCURSOR)
RETURN pipeliner.title_collection PIPELINED IS
  counter           NUMBER := 1;
  active_record     PIPELINER.TITLE_STRUCTURE;
  active_collection PIPELINER.TITLE_COLLECTION:=
                      pipeliner.title_collection();
BEGIN
  -- A simple loop allows you to fetch the already open cursor
  LOOP
    FETCH incoming_cursor INTO active_record;
    EXIT WHEN incoming_cursor%NOTFOUND;
    -- Extend collection, assign a value.
    active_collection.EXTEND;
    active_collection(counter) := active_record;
	-- Add a row to the aggregate return table.
    PIPE ROW(active_collection(counter));
    counter := counter + 1;
  END LOOP;
  CLOSE incoming_cursor;
  RETURN;
END;
/

The PIPE command transfers a scalar variable or record structure into a row of a target aggregate table, which can be read through the TABLE command in a SQL statement. A neat thing that happens in the example, is that it simply transfers the whole row returned by the cursor as an instance ACTIVE_RECORD into an indexed element of the ACTIVE_COLLECTION collection. The alternative would be syntax like the following:

active_collection(counter).item_title := active_record.item_title;
active_collection(counter).item_title := active_record.item_subtitle;

While those are neat features that may be copied from example code, they’re useless. The more direct way to create this translation program eliminates the collection entirely.

-- A function that receives a cursor as an input and returns an aggregate table.
CREATE OR REPLACE FUNCTION use_of_input_cursor
(incoming_cursor SYS_REFCURSOR)
RETURN pipeliner.title_collection PIPELINED IS
  counter           NUMBER := 1;
  active_record     PIPELINER.TITLE_STRUCTURE;
BEGIN
  -- A simple loop allows you to fetch the already open cursor
  LOOP
    FETCH incoming_cursor INTO active_record;
    EXIT WHEN incoming_cursor%NOTFOUND;
	-- Add a row to the aggregate return table.
    PIPE ROW(active_record);
  END LOOP;
  CLOSE incoming_cursor;
  RETURN;
END;
/

Another alternative would be to do a BULK COLLECT directly into the collection. The next example demonstrates that but you should note that you can’t use a FORALL when assigning collection values to a PIPE for a pipelined function.

-- A function that receives a cursor as an input and returns an aggregate table.
CREATE OR REPLACE FUNCTION use_of_bulk_cursor
(incoming_cursor SYS_REFCURSOR)
RETURN pipeliner.title_collection PIPELINED IS
  active_collection PIPELINER.TITLE_COLLECTION:=
                      pipeliner.title_collection();
BEGIN
  -- A bulk collect fetch the already open cursor
  FETCH incoming_cursor BULK COLLECT INTO active_collection;
  FOR i IN 1..active_collection.COUNT LOOP  
	-- Add a row to the aggregate return table.
    PIPE ROW(active_collection(i));
  END LOOP;
  CLOSE incoming_cursor;
  RETURN;
END;
/

4. In SQL, you can query the result from either the use_of_input_cursor using the following syntax:

-- Query the results.
SELECT *
FROM TABLE(use_of_input_cursor(weakly_typed_cursor('Harry')))
/

… or use_of_bulk_cursor using the following syntax:

-- Query the results.
SELECT *
FROM TABLE(use_of_bulk_cursor(weakly_typed_cursor('Harry')))
/

You can change the prior query to call the use_of_bulk_cursor function to test the bulk collect operation. It didn’t seem worth the effort of doing another screen shot.

More or less, the limitation exists because when operating exclusively inside of PL/SQL there is no way currently to support an Adapter OO pattern. You can implement an Adapter pattern if you use an OCI8 external programming language to read the cursor as a dynamic result set (really only a multi-dimensional array of strings – or technically because the index is sparse a list).

Alternatively in a PL/SQL only solution, you can simply use a strongly typed cursor and bundle all the components into a single program unit. Assuming you create a two column view from the ITEM table, you can use the following query (code base can be downloaded from McGraw-Hill for the Oracle Database 11g PL/SQL Programming book):

DECLARE
  TYPE cursor_record IS RECORD
  ( item_title    item.item_title%TYPE
  , item_subtitle item.item_subtitle%TYPE );
  t CURSOR_RECORD;
  TYPE strong_type IS REF CURSOR RETURN item_title%ROWTYPE;
  c STRONG_TYPE;
BEGIN
  OPEN c FOR SELECT item_title, item_subtitle FROM item;
  LOOP
    FETCH c INTO t;
      EXIT WHEN c%NOTFOUND;
      DBMS_OUTPUT.put_line(t.item_title);
  END LOOP;
  CLOSE c;
END;
/

If you like this, you should check out another wrapping technique here. If you’d like another system reference example, post a comment to let me know.