Oracle – for when it was like that when you got there

Main menu

Post navigation

Getting APEX to play with Ref Cursors

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.

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 :

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.

8 thoughts on “Getting APEX to play with Ref Cursors”

The best of all is that using pipeline is the best interface when the same data has to be shown in different outputs.
In mine case that was: SQL*Plus script, Oracle Forms/Reports and Apex …. with same source and centralized change management.
🙂

there’s a couple of ways I can think of. The first ( and probably simplest) is to create a view and then base your type on that view in the package.

The second is to declare a Record in the package.
Taking the HR schema as our example, if you wanted to return the EMPLOYEES first_name and last_name, together with the DEPARTMENTS department_name, you could do something like this…

So, create a record and then base the table type on that record.
Because the record is based on anchored declarations, we still have the security that it won’t break if the column definitions are changed.

To answer (2) first – fair point – the ref_cursor should really be closed after the pipelined table has been populated. I’ve amended the post accordingly.

As for (1) – I’m using ref cursors because I had a load of ref_cursor functions lying around from my previous web application ( PHP/JEE etc) and wanted to re-use them in APEX without having to re-write all that code again.

As for a plain old cursor to populate a pipelined table. Yes, it would. At this point, I was going to point you to a URL for a suitable example of this but couldn’t find one so…