MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Reference Cursors – Why, when, and how?

with 7 comments

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.

Written by maclochlainn

October 11th, 2008 at 6:03 am

7 Responses to 'Reference Cursors – Why, when, and how?'

Subscribe to comments with RSS or TrackBack to 'Reference Cursors – Why, when, and how?'.

  1. I am trying to use table functions as a way to stop replicating large volumes of data into my operational data store and evolve into a more federated or EII environment. Some source systems of ours only have web services available as a means to retrieve data so what I have been able to do so far to get at their information from my database using SQL is:

    Oracle view -> Oracle package/pipelined table function -> Oracle java -> Source system web service

    Works great and fast when I issue ‘select * from view where id = 1′. The problem is I cannot prevent an individual or a software tool from issuing ‘select * from view’. Without the where clause the table function will bog down my database and network trying to pull back hundreds of millions of rows. I cannot try to artificially scale back the table function’s incoming ref cursor using rownum because someone could ask the legit question ‘select * from view where month = 1′ and get back an incomplete set of data. Any advise or experience in this regard that you could offer me?

    Richard

    28 Apr 09 at 4:03 pm

  2. You’ve posed an interesting question but do your users really want a million records back to a web form?

    I’d suggest if you query the possible number of row returns before querying the data. When the number or rows exceeds a reasonable set, prompt the end-user with the opportunity to download a CSV file. If they say yes, then use the LIMIT syntax like this to write blocks of rows that fit within your allocate SGA memory constraints. I’d recommend an external table because you can perform bulk writes. Make sure you set up the external table with SQL*Loader not Data Pump. You could also use a Java program to write it to a non-Oracle owned directory with something like this blog post.

    OPEN c;
    LOOP
      FETCH c BULK COLLECT INTO record_type LIMIT 1000;
      EXIT WHEN record_type.COUNT = 0;
      FOR i IN 1..record_type.COUNT LOOP
        ... WRITE logic ...
      END LOOP;
    END LOOP;

    Since you’re in Java it might be more effective to simply write the data streams to the middle-tier. I hope this helps but if I misunderstood let me know.

    maclochlainn

    28 Apr 09 at 11:08 pm

  3. Is there way of using table functions when you don’t know what kind of TABLE OF TYPE you will be piping back?

    For example, imagine I have a package. That package contains a function that accepts arguments, builds a dynamic SELECT and stores the results of that SELECT in a REF CURSOR.

    This is where the problem starts. I now want to pipeline the REF CURSOR rows. Using the REF CURSOR as the function output, and doing a TABLE(function) on it doesn’t work – as you showed in your article.

    So I thought, why not dynamically build a new type (DYNTYPE) on the basis of your REF CURSOR metadata inside your package. This could happen inside an extra procedure which you call at the end of your first function.

    You would then write an extra function that accepts the REF CURSOR as input and pipelines a TABLE OF DYNTYPE, and we’re done.

    But the question is – how do you create a type on the basis of REF CURSOR metadata inside a package?

    And if this approach doesn’t work, can you make any other suggestion for pipelining when the number and types of underlying columns of your REF CURSOR is unknown at design time?

    This would be a very flexible tool.

    (In my case it’s actually the only way to go because my client application (S-Plus) will only receive data if you hard-code a SQL statemtent or if you pipeline a table function back to it.)

    Guy

    6 Feb 10 at 3:52 pm

  4. What you’d like to do is implement the Adapter Patter natively in PL/SQL. I would like that functionality too, but it doesn’t exist. If you’re client tool can query a result set through OCI8, like the link to an early post, then you can do it. The only way I’ve been able to accomplish it is through OCI8 with PHP. I’m sure it’ll work in C/C++ too, and I’ve heard it works in C#.

    maclochlainn

    6 Feb 10 at 7:28 pm

  5. nice article, reflects my exact feeling about all the collection thing in Oracle PL/SQL. Thought I was being lazy and just using ref cursor everywhere, so you kind of confirmed my thinking. But I am puzzled by this: “This is known as an associative array, and commonly called a PL/SQL table.” I am cramming for the interviews, and forced to read thru the whole Collections chapter in Oracle 10 documentation to make sense of what the difference between associative, VARRAY and PL/SQL Table is, so now I am confused. Are you saying PL/SQL Table and Associative array are the same? I kind of doubt it but let you shine the lite. thank you in advance
    JG

    Jenny G.

    29 Dec 10 at 7:09 pm

  6. They were once called PL/SQL tables (principal labeling in Oracle 8 documentation) and mixed notation in Oracle 8i documentation). Oracle discontinued the labeling and changed to index-by tables in Oracle 8i. Then, again they changed to associative arrays with Oracle 10g. I probably should say among developers who predate Oracle 10g.

    Basic difference list:

    • Varray can be limited to a maximum number of elements and may consist of scalar data types or object types. It’s the most like a traditional array in other programming languages. They run in both SQL and PL/SQL context (or engines).
    • Nested tables aren’t limited to a maximum number of elements and may consist of scalar data types or object types. It’s the most like a traditional doubly linked list in other programming languages that manage exclusively base types (primitives) or object types. They run in both SQL and PL/SQL context (or engines).
    • Associative arrays aren’t limited to a maximum number of elements and may consist of scalar data types or PL/SQL record types. They’re limited to use exclusively inside the PL/SQL engine.

      maclochlainn

      29 Dec 10 at 10:31 pm

    • yep.

      Jenny G.

      30 Dec 10 at 11:20 am

    Leave a Reply