Getting APEX to play with Ref Cursors from smithers on DBPedias

It’s that time of year again. Things are a bit tense around the house.
The other morning, I woke up to find that someone had placed a leek in my slippers.
Yes it’s Six Nations time again. England are playing Wales on Saturday. The lovely Debbie is getting into the spirit of the occasion…by exhibiting extreme antagonism to all things English.

Whilst the patriot in me would like to cheer on the Red Rose on Saturday, I have decided that discretion ( or in this case, cowardice) is the better part of valour and will instead, sit quietly in the corner, hoping for a draw. That way, I’ve not sold out completely and next week will be far more pleasant if Wales have not lost.

For those readers who know Rugby Union as merely another one of those odd games that we English let our former colonies win at, all you need to know is, the Welsh take this sport very seriously.

In the meantime, I’m trying to keep a low profile, which means playing around with APEX 4.1.

The heady excitement of discovering the first decent GUI development environment for PL/SQL programmers since Oracle Forms is now starting to be replaced by some of the harsh realities of modern web development.
For example, how can I reuse all those terribly useful functions that return Ref Cursors ?
I mean, they work fine in PHP and various other languages, and APEX itself is written in PL/SQL. Should be easy, shouldn’t it ?

Er, no.

APEX simply refuses to play. “I laugh in the face of your weakly typed Ref Cursor” it seems to say. Clearly, some persuasion is required if I’m not to end up with a lot of code locked away in my APEX application, unusable by any other programming language I might want to use to build a web front-end for my database.
The way to an APEX application’s heart is, as will become apparent, through Pipelined functions.

A Simple Example

Let’s say we have a table called simple. It’s created like this :

CREATE TABLE simple (
	first_name VARCHAR2(30),
	msg VARCHAR2(50))
/

INSERT INTO simple( first_name, msg) VALUES('MIKE', q'[Oh, it's you]')
/

INSERT INTO simple( first_name, msg) VALUES('DEB', 'Hey gorgeous!')
/

COMMIT
/

We’ve also got a function that PHP plays nicely with, which returns a Weakly Typed Ref Cursor :

CREATE OR REPLACE FUNCTION get_simple_fn RETURN SYS_REFCURSOR 
IS
	l_ret_rc SYS_REFCURSOR;
BEGIN
	OPEN l_ret_rc FOR
		SELECT first_name, msg FROM simple;
	RETURN l_ret_rc;
END;
/

We want to reuse this function in our APEX application. For this purpose, we need to turn to a technique more usually associated with ETL, the pipelined function.
To do this, we will need some or all of :

a database Object Type
a database Table Type of the Object type
a pipelined function to act as a wrapper for the Ref Cursor
a giant inflatable plastic daffodil

Deb was looking over my shoulder so I had to add that last item.

The Database Types Method

Create the Database Object Type

CREATE OR REPLACE TYPE simple_typ AS OBJECT (
	first_name VARCHAR2(30),
	msg VARCHAR2(50))
/

And now for the Table of objects type…

CREATE OR REPLACE TYPE simple_tab_typ IS TABLE OF simple_typ
/

Finally, the pipelined function to act as a wrapper…

CREATE OR REPLACE FUNCTION simple_pipe_fn( i_cursor SYS_REFCURSOR)
	RETURN simple_tab_typ PIPELINED IS
	l_row simple_typ := simple_typ(NULL, NULL);
BEGIN
	LOOP
		FETCH i_cursor INTO l_row.first_name, l_row.msg;
		EXIT WHEN i_cursor%NOTFOUND;
		PIPE ROW( l_row);
	END LOOP;
	RETURN;
END;
/

So, the Pipelined function takes a REF CURSOR as an argument and returns a value of the table type we’ve just created.

Now we test this little lot from SQL …

SELECT * 
FROM TABLE(simple_pipe_fn(get_simple_fn)); 

FIRST_NAME MSG 
---------- -------------------- 
MIKE	   Oh, it's you 
DEB	   Hey gorgeous!

Let’s see what APEX makes of all this…

In Application Builder, go to whatever your playground application is and Create Page
I’m going for an Interactive Report.

Page Name is Pipeline Test
Region Name is Simple.

Now to enter the select statement :

Come on, eat you're Ref Cursors or you'll never grow up to be a proper web technology

Click through the rest of the creation wizard and then run it …

There's a good little declarative development environment.

OK, not the most elegant report ever, but it does actually work.

There are one or two things that are a bit unsatisfactory with this approach ( apart from the obvious drawback of having to persuade a PL/SQL development tool to play with a Ref Cursor).
First off, this database type business. Well, it’s not exactly robust, is it. If I change the table definition, I’ll need to remember to change the object type as well.

The other minor niggle is, well, there does seem to be quite a bit of type-ing. Ahem.
Moving swiftly on, let’s see if we can solve both of these issues in one fell swoop….

And now for something completely different…

Right, we’re going to drop those boring fuddy-duddy database types we’ve just created and use a PL/SQL package instead.

DROP TYPE simple_tab_typ 
/ 

Type dropped 

DROP TYPE simple_typ 
/

Type dropped

Now to replace these with a package header :

CREATE OR REPLACE PACKAGE pipe_types_pkg AS
	TYPE simple_tab_typ IS TABLE OF simple%ROWTYPE;
END pipe_types_pkg;
/

As we’ve declared this type in a package, we can use an anchored declaration to base it on the table.
If the table structure changes, so will the type.

Finally, we need to change the pipelined function to reference the type we’ve declared in the package :

CREATE OR REPLACE FUNCTION simple_pipe_fn( i_cursor SYS_REFCURSOR)
	RETURN pipe_types_pkg.simple_tab_typ PIPELINED IS
	l_row simple%ROWTYPE;
BEGIN
	LOOP
		FETCH i_cursor INTO l_row.first_name, l_row.msg;
		EXIT WHEN i_cursor%NOTFOUND;
		PIPE ROW( l_row);
	END LOOP;
	RETURN;
END;
/

If we now test this again …

SQL> SELECT * FROM TABLE(simple_pipe_fn(get_simple_fn)); 

FIRST_NAME MSG 
---------- -------------------- 
MIKE	   Oh, it's you 
DEB	   Hey gorgeous! 

SQL>

It must be said that, even though we haven’t explicitly created a database type , Oracle has taken matters into it’s own hands

SELECT object_name, object_type 
FROM user_objects 
WHERE TRUNC(created) = TRUNC(SYSDATE)
/
SYS_PLSQL_27148_21_1	       TYPE 
SYS_PLSQL_27148_DUMMY_1        TYPE 
SYS_PLSQL_27158_9_1	       TYPE 
SYS_PLSQL_27158_DUMMY_1        TYPE 
PIPE_TYPES_PKG		       PACKAGE 
SIMPLE_PIPE_FN		       FUNCTION 
GET_SIMPLE_FN		       FUNCTION 
SIMPLE			       TABLE

Hmmm, not sure why it’s felt the need to define four types on my behalf.
On the plus side, we can leave the database to look after it’s own types. Ours is all future-proofed and low maintenance.

Of course, you have the option of declaring all of your required types in a single package header, or leaving them in the packages in which your Ref Cursor functions reside.

To prove a point, let’s see what happens if we make a change to the simple table :

ALTER TABLE simple MODIFY (
    msg VARCHAR2(100))
/

Let’s see how our package version of the code copes :

SELECT * 
FROM TABLE( simple_pipe_fn( get_simple_fn)) 
/ 

FIRST_NAME MSG 
---------- -------------------- 
MIKE	   Oh, it's you 
DEB	   Hey gorgeous! 

SQL>

Running the report in APEX shows a similar lack of concern for the change in the table structure.

Whilst all this does mean that you have to create an API for the web API you’ve already got, it does mean that you can re-use the code in APEX.

Deb has just wandered by humming “Land of My Fathers” which I will take as my queue to run away and hide in the cupboard under the stairs for a bit.