Using a refcursor in Apex

My client had a running portal, build in Delphi. Requests for data were implemented as calls to stored packaged functions returning refcursors to a soapserver. For obvious reasons my client wanted to renew the front end of the portal with Apex. The backend however the refcursors- had to be untouched.

I came up with the idea of using some of the XDB features of the database.

This simple example using EMP should give you some idea of what we did.

First, we create a package with a stored function returning a refcursor

create or replace package demo_ref
is

function give_employees
  return sys_refcursor;

end;
/

create or replace package body demo_ref
is

function give_employees
  return sys_refcursor
is
  l_return sys_refcursor;
begin
  open l_return
  for
    select empno, ename
    from   emp
  ;
  return l_return;
end;

end;/

In an apex-page we create an SQL-region using the following-like SQL:

select x.empno
,      x.ename 
from  xmltable ( '/ROWSET/ROW' 
                 passing xmltype(demo_ref.give_employees) 
                 columns empno  number(10) path 'EMPNO'
                 ,       ename  varchar2(100) path 'ENAME'  
               ) x

Of course you could also just test the SQL in your favorite IDE.

Advertisements

4 thoughts on “Using a refcursor in Apex

  1. I’m trying your example, but my sql, running from Toad or sqlplus, only returns blank rows. The row count is actually correct, but nothing is being displayed. DB is 11.2.0.1.

    • The column names after “path” have to match exactly and must be in Uppercase. It works now. This is a much simpler and easier solution than using pipeline functions for using ref cursors. thanks

  2. I am having one issue with this. It’s not closing the ref cursors. Unlike other methods,
    you close the cursor, but here you don’t.

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