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/