Create GET-Resource Handler using PL/SQL Cursor in Oracle APEX RESTful Services

Sometimes you want to provide Data as REST Services from Oracle APEX. This may be an easy task if you need to publish one Table/View directly. But maybe you have some older PL/SQL logic with cursors and more that needs to be published as REST.

In one Project we had to migrate an CSV-Export to REST with Oracle APEX. The old Script to produce the CSV must be reused.

The first case is easy:
Go to the RESTful Data Services, create a Module and Template. Next, create a Resource Handler “GET” with Source Type “Collection Query” and use a simple Query to get the Data from the Table/View.
That’s it.
APEX will now translate your Query to JSON automatically to create the REST Resource.

If you have a more complex Logic with Cursors you want to reuse you can still use the same Source Type and work with a Pipelined Function instead.

So I took the old Script and reworked it a little bit to get the Piplined Function to work.

I prefere to use Packages.

New Package Spec:

create or replace package rest_pkg as

    -- create record
    type t_result_rec is record (
        empno       number
      , ename       varchar2(255)
      , job         varchar2(255)
      , calc        number
    );

    -- create type
    type t_result_tab is table of t_result_rec;

    -- create piplined function
    function get_all_emp return t_result_tab pipelined;

end rest_pkg;
/

Package Body:

create or replace package body rest_pkg
as

    function get_all_emp
    return t_result_tab pipelined
    as

      cursor curs1 is select empno, ename, job, sal from emp;

      c curs1%rowtype;

      l_cur_row   t_result_rec;
      l_empty_row t_result_rec;
      l_count     number;
      l_calc      number;

    begin

      l_count := 0;

      open curs1;
      loop

        fetch curs1 into c;
        exit when curs1%notfound or curs1%notfound is null;

        -- initialize the row with empty values
        l_cur_row := l_empty_row;

        /* 
        I am hiding the real calculation here, but here is where the
        magic happens. This is the reason why I cannot use a simple
        query for the REST Source and have to use this old logic.
        */

        -- Set current row values
        l_cur_row.empno := c.empno;
        l_cur_row.ename := c.ename;
        l_cur_row.job   := c.job;
        l_cur_row.calc  := c.sal * 1.1; 

        -- return the row
        pipe row(l_cur_row);
 
        l_count := l_count + 1;

      end loop;
      close curs1;

    end get_all_emp;

end rest_pkg;
/

Now we are able to use the Function in the Handler Source: select * from table(rest_pkg.get_all_emp);

Checking the Result first … looking good:

Now use the Query in the Handler:

That’s it. You can now use the GET-Resource.

Resources: https://oracle-base.com/articles/misc/pipelined-table-functions, https://www.thatjeffsmith.com/archive/2021/03/four-options-for-working-with-ords-plsql-refcursors/