Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

Posts Tagged ‘system reference cursor

Adapter or not adapter that’s the question

with 2 comments


The Adapter pattern in Object-Oriented OO programming is missing when it comes to how you can read, pass, and manage a PL/SQL system reference cursor. At least, it’s missing for PL/SQL native development. It is available through the OCI but only as a multiple dimensional array of strings.

Here’s an illustration of the Adapter Pattern (courtesy of the Design Patterns book by Gamma, Helm, Johnson, and Vlissides):

There are a few ways to handle reference cursor in PL/SQL. I’ve updated the earlier blog to more completely cover the options. In all cases within a PL/SQL environment, you must know the target type of the PL/SQL record structure. The target for converting a PL/SQL reference cursor is a a PL/SQL record structure.

You can assign a PL/SQL record structure through a PIPELINED table function to an aggregate table, which can be read by SQL. The following query lets you read an aggregate table back into a PL/SQL structure but begs the question of why you’d want to do that. The actual query by itself is in the updated blog entry linked above.

The OCI8 driver lets you take the system reference cursor from a PL/SQL block and translate it to a multidimensional array of strings. The following (borrowed from my book on PHP programming with Oracle) demonstrates how to open a statement and a reference cursor, which lets you manage the OUT mode parameter (covered in this earlier post) variable of a PL/SQL reference cursor. Our thanks should go to the OCI team because they see the world of interactivity.

You can query the results of the reference cursor ($rc), like this:

Moreover, it would be wonderful if Oracle let you implement a full Adapter pattern but there are constructive ways to work with what we’ve got now. You actually get a bit more through the JDBC implementation but that’s for another blog I guess. As to adapter or not adapter, it’s clearly not.

Written by maclochlainn

October 31, 2008 at 11:00 pm

Reference Cursors – Why, when, and how?

with 3 comments


Quick Update: I move the blog because folks told me the screen shots weren’t as useful as copiable and formatted code. You can find both and new content in the updated blog posting here, I apologize for the inconvenience but there wasn’t a way to redirect you from WordPress.

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. 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:

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

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):

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.

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.

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.

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

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):

If you’d like another example, post a comment to let me know.

Written by maclochlainn

October 11, 2008 at 6:03 am