Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

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

2 Responses

Subscribe to comments with RSS.

  1. Hello Michael,

    Lots of interesting material in your blog! Thanks for sharing…

    One thing. You write “You can assign a PL/SQL record structure through a PIPELINED function to an aggregate table, which can be read by SQL.”

    I believe you mean “through a table function”. A pipelined table function is a specialized sort of table function, which is very helpful for parallelizing execution of queries that call a table function, and for reducing pereceived elapsed time of data retrieval.

    It does not, however, provide a benefit over “regular” table functions except in those circumstances. And pipelined functions can ONLY be called from within queries.

    Regards, Steven Feuerstein

    Steven Feuerstein

    November 1, 2008 at 2:15 pm

  2. Hey Steven,

    You make a great point! I’ve updated the reference to: PIPELINED table function. That’s definitely more consistent with Chapter 12 of the PL/SQL Language Reference. I tried to make that point about where they’re most useful in an earlier post but clarification is always welcome. Thanks!


    November 1, 2008 at 3:32 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: