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.