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.

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;
	CLOSE i_cursor;
	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;
	CLOSE i_cursor;
	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.

About these ads

8 thoughts on “Getting APEX to play with Ref Cursors

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

  2. Hello there,

    What would happen if we have a query that joins more than one table. Is there any way that Oracle creates automatically this kind of types as well?

    Thanks
    FJ

    • FJ,

      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…

      First, the package :

      CREATE OR REPLACE PACKAGE hr_types_pkg AS
      	TYPE rec_emps IS RECORD(
              first_name hr.employees.first_name%TYPE,
         	    last_name hr.employees.last_name%TYPE,
              department_name hr.departments.department_name%TYPE);
      
          TYPE emp_tab_typ IS TABLE OF rec_emps;
      END hr_types_pkg;        
      

      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.

      Next up is the pipelined function :

      CREATE OR REPLACE FUNCTION hr_pipe_fn( i_cursor SYS_REFCURSOR)
      	RETURN hr_types_pkg.emp_tab_typ PIPELINED IS
      	l_row hr_types_pkg.rec_emps;
      BEGIN
      	LOOP
      		FETCH i_cursor INTO l_row.first_name, l_row.last_name,
      		    l_row.department_name;
      		EXIT WHEN i_cursor%NOTFOUND;
      		PIPE ROW( l_row);
      	END LOOP;
      	RETURN;
      END;
      /
      

      Here, we’re declaring the row variable as being the type of the record we created in the package.
      Finally, the function to return the REF CURSOR :

      CREATE OR REPLACE FUNCTION get_emp_fn
          RETURN SYS_REFCURSOR
      IS
          l_ret_rc SYS_REFCURSOR;
      BEGIN
          OPEN l_ret_rc FOR 
              SELECT emp.first_name, emp.last_name, dept.department_name
              FROM employees emp, departments dept
              WHERE emp.department_id = dept.department_id;
          RETURN l_ret_rc;
      END;
      /
      

      As I said, creating a view is probably the simplest way to go, but you may find circumstances where this method is preferable.

      HTH

      Mike

  3. Two questions. 1) Why use a ref cursor? Wouldn’t a plain old cursor work to populate the pipelined table? 2) Why not close ref cursor when you are done populating the pipelined table?

    Regards,
    Bill

    • Bill,

      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…

      CREATE OR REPLACE FUNCTION simple_pipe_fn
          RETURN simple_tab_typ PIPELINED IS
          l_rtn_row simple_typ := simple_typ(NULL, NULL);
      BEGIN
                  FOR r_row IN (SELECT first_name, msg FROM simple) LOOP
              --
              -- Oracle objects if you try to return the r_row
              -- variable ( PLS-00382 - expression is of wrong type)
              -- so...
              --
              l_rtn_row.first_name := r_row.first_name;
              l_rtn_row.msg := r_row.msg;
                              PIPE ROW( l_rtn_row);
                  END LOOP;
                  RETURN;
      END;
      /
      

      Apologies for the shonky formatting, but you get the idea.
      Thanks for the tip.

      Mike

  4. Can you skip the cursor and do a bulk collect into a table type and then return that?
    Can you modify this example to do that?

    declare
        type my_rec is record (tname user_tables.table_name%TYPE);
        type my_table is table of my_rec;
        list my_table;
    begin
        select table_name 
        bulk collect into list 
        from user_tables;
    end;
    
    • joemon,

      I’ve knocked up the following example using the HR schema.

      First we need to declare the type in a package.
      This let’s us use an anchored declaration whilst also enabling us to use the
      decalared type as the return type for the Function…

      CREATE OR REPLACE PACKAGE my_types_pkg IS
          TYPE my_rec IS RECORD
          (
              table_name user_tables.table_name%TYPE
          );
          
          TYPE my_table IS TABLE OF my_rec;
      END my_types_pkg;
      /
      

      Now for the function itself…

      CREATE OR REPLACE FUNCTION get_tabs_fn
          RETURN my_types_pkg.my_table PIPELINED
      IS
          tbl_tabs my_types_pkg.my_table;
      BEGIN
          --
          -- Populate the array
          --
          SELECT table_name BULK COLLECT INTO tbl_tabs
          FROM user_tables;
          FOR i IN 1..tbl_tabs.COUNT LOOP
              PIPE ROW(tbl_tabs(i));
          END LOOP;
          RETURN;
      END;
      /
      

      Now, if we invoke the function from SQL*Plus…

      SQL> SELECT * FROM TABlE(get_tabs_fn);
      
      TABLE_NAME
      ------------------------------
      REGIONS
      LOCATIONS
      DEPARTMENTS
      JOBS
      EMPLOYEES
      JOB_HISTORY
      COUNTRIES
      
      7 rows selected.
      
      SQL> 
      

      HTH

      Mike

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s