I’ve avoided writing this post for a few years now, but no longer! Oracle Database REST APIs served up from ORDS have paging of the results built-in, IF you are using SQL based handlers.
So using SQL to define a REST API on a GET handler, for example, you only need to provide that SQL. This is handled, automatically.
But as awesome as SQL is, we sometimes have the need to instead use PL/SQL. And one of the things PL/SQL can do, is run SQL. In this case, we’re looking at a SYS_REFCURSOR.
Now, I’ve shown before how to get a SYS_RECURSOR back from a PL/SQL block of code in your REST API. BUT, there was no paging there, so if the refcursor has 5000 rows in it, so be it, that’s what you’re going to get.
Give me paging, already!
You just need to built it into your PL/SQL program, and into your handler. Community member and hero, Oli, shows how to do this briefly in our ORDS forums.
What we’ll need to accommodate this is:
- PL/SQL stored procedure with at least one OUT SYS_REFCURSOR
- said procedure accommodates inputs to shape the SQL behind the cursor
- paging logic
- ability to pass values to specify where we want to start and how many rows to grab
- provide link(s) to move about the pages
- include the paging attributes like shown above (hasMore, limit, offset, count…)
My PL/SQL program
No worries, I’ll share ALL of the source below at the end of the post. I find it’s easier to ‘read’ the code via screenshots sometimes.
Warning: ordering of queries isn’t guaranteed
I don’t have an ORDER BY clause in my SELECT statement, but I SHOULD. Since I don’t, it’s quite possible that I could get records moving around different pages, so they could show up twice or more!
Luckily, this is an EASY FIX. I can do an ORDER BY either CREATED_AT or CHECKIN_ID, ASC.
Just running the stored procedure, no REST API
And if I run my new SP using my old friend, SQL Developer, we can see I can get a ‘window’ of the contents of my UNTAPPD table.
Now let’s build our REST API!
First I need to sort my REST API Template URI and Handler.
I’ll go with a GET on paged/
Input Parameters
To run the stored procedure, we need to feed two values to it, and the easiest way to to that for a GET handler, is to include them as parameters on the URI.
But I should declare those, so it’s clear what’s happening.
For example, my GET request could look like –
ords/beers/examples/paged/?skip=101&read=3
Handler Code
So obviously I need to build up the code to invoke the PL/SQL program, and then I also need to sort the output, and build out the JSON response that will be returned to the API caller. Let’s look paging portion of the code –
So if someone calls my API, without paging parameters, I want them to default to the first 25 records.
We call the SP with those inputs, from the URI parameters, if included.
I calculate what the offset would be for the ‘next’ page, basically add the current pageize to the current offset.
I include a first link. I am NOT calculating the PREV link, it’s later afternoon and my IF THEN ELSIF brain started hurting. I got some help from AI to calculate the PREV link, and it seems to work pretty good. See below for the logic and examples in the Scenarios.
Knowing how big a REFCURSOR is or if there are more rows to fetch is a challenge, so I’m punting on those, but someone asked how to include those parameters on a PL/SQL handler response, and this is how to do it (declare REST Handler Parameters, set the values appropriately).
About those :bind variables
Most of those are sorting the output or response of the REST API. It’s one thing to have the handler code call my stored procedure, but I must tell ORDS what do to with the results, or how to obtain the results. This is the #1 benefit of our AUTO feature for PL/SQL – we sort that for you!
Our output parameters
We have a few ‘tricks’ employed here, but here’s what we have –
Things to remember, keep in mind:
- The order of the OUT parameters determines the order they are delivered/presented in the JSON response payload
- A name that starts with a ‘$’ will generate a LINK, assuming it’s an OUT RESPONSE
- parameter type ‘RESULTSET’ takes the refcursor and uses our JSON logic to give you the items array, just like you see for a SQL query based handler
Ok, let’s do a few examples!
Scenario 1: Default, no paging requested
GET/ords/beers/examples/paged/
Scenario 2: The ‘next’ page
So jump ahead 25 records, and get 25 more.
GET /ords/beers/examples/paged/?skip=25&read=25
Scenario 3: A random page offset and size
Ok, let’s skip 1775 records and grab 2 only.
GET /ords/beers/examples/paged/?skip=1775&read=2
Previous Page!
We have the current page of results, we need to know what the next page is, right? But we might also want to know how to get the previous page.
And, if we’re sitting on page 1 or have a small offset but a big skip, we don’t want to send someone BACK to a negative skip count, like /paged?skip=-3 … instead we’ll just omit the prev link.
The logic for calculating the previous page is fairly straightforward.
First, we’ll do some maths.
prev_link := greatest(param1 - param2, 0);I’m going to take the largest of two numbers, the difference of skip parameter of the current page and read count, OR ZERO.
That way if that difference is -27, we’ll go with 0 instead.
And then I’ll just check to make sure that’s not set to ZERO before generating a value of the prev link.
if param1 <> 0 THEN
:prev := '../paged?skip=' || prev_link || '&read=' || next_link_param2;
end if;First vs Second vs Third Pages.
All the code, already
Ok here you go. As always, don’t copy and paste into production. This is all shown as an example of what CAN BE DONE with your REST APIs. I assume you are a better PL/SQL programmer than me, but if you’re new, be sure to ask someone for code review!
My code review comes from the hater comments following this post, haha. Just kidding, it’s caring to share your feedback!
The original PL/SQL program
This assumes you have a table, with enough data for paging to make sense. So like, 3 rows. I just happen to have a couple a thousand in my UNTAPPD table.
create or replace procedure plsql_beers (
data out SYS_REFCURSOR,
starting_with in integer default 0,
how_many in integer default 25) is
data_out SYS_REFCURSOR;
-- feed inputs to cursor query for offset and pagesize
BEGIN
open data_out FOR
select *
from UNTAPPD
offset starting_with rows fetch next how_many rows only;
data := data_out;
end plsql_beers;The REST Module, template, handler, and parameters.
If you copy and paste this, you’ll need to change out the table name, and note I’m including REST Privs, so this will be LOCKED DOWN, by default.
set define off;
-- Generated by ORDS REST Data Services 23.3.0.r2891830
-- Schema: BEERS Date: Wed Dec 06 08:58:25 2023
--
DECLARE
l_roles OWA.VC_ARR;
l_modules OWA.VC_ARR;
l_patterns OWA.VC_ARR;
BEGIN
ORDS.ENABLE_SCHEMA(
p_enabled => TRUE,
p_schema => 'BEERS',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'beers',
p_auto_rest_auth => TRUE);
ORDS.DEFINE_MODULE(
p_module_name => 'examples',
p_base_path => '/examples/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'examples',
p_pattern => 'paged/',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'examples',
p_pattern => 'paged/',
p_method => 'GET',
p_source_type => 'plsql/block',
p_mimes_allowed => NULL,
p_comments => NULL,
p_source =>
'DECLARE
param1 integer := :skip;
param2 integer := :read;
next_link_param1 integer;
next_link_param2 integer;
prev_link integer;
-- dealing with :binds can be fun, so I''m declaring local variables to deal with those
BEGIN
-- if request comes in w/o number of rows requested, we''ll default to the
if param1 is null THEN
param1 := 0;
end if;
if param2 is null THEN
param2 := 25;
end if;
-- call the program!
-- we have a parameter for the REST handler of type OUT RESULTSET to deal
plsql_beers(data => :response1,
starting_with => param1,
how_many => param2);
-- there is little to no paging logic, but
-- to follow link to ''next page'', take current offset and add to it the number of rows fetched
-- brain hurts, skipping the PREV logic
next_link_param1 := param1 + param2;
next_link_param2 := param2;
prev_link := greatest(param1 - param2, 0);
:next := ''../paged?skip='' || next' || '_link_param1 || ''&read='' || next_link_param2;
if param1 <> 0 THEN
:prev := ''../paged?skip='' || prev_link || ''&read='' || next_link_param2;
end if;
:first := ''../paged/'';
:more := ''Mayhaps'';
-- going to lie with count, no way to know for sure, default to number of rows requested
:count := next_link_param2;
:offset := param1;
:limit := param2;
end;');
ORDS.DEFINE_PARAMETER(
p_module_name => 'examples',
p_pattern => 'paged/',
p_method => 'GET',
p_name => 'items',
p_bind_variable_name => 'response1',
p_source_type => 'RESPONSE',
p_param_type => 'RESULTSET',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'examples',
p_pattern => 'paged/',
p_method => 'GET',
p_name => 'hasMore',
p_bind_variable_name => 'more',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'examples',
p_pattern => 'paged/',
p_method => 'GET',
p_name => 'limit',
p_bind_variable_name => 'limit',
p_source_type => 'RESPONSE',
p_param_type => 'INT',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'examples',
p_pattern => 'paged/',
p_method => 'GET',
p_name => 'offset',
p_bind_variable_name => 'offset',
p_source_type => 'RESPONSE',
p_param_type => 'INT',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'examples',
p_pattern => 'paged/',
p_method => 'GET',
p_name => 'count',
p_bind_variable_name => 'count',
p_source_type => 'RESPONSE',
p_param_type => 'INT',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'examples',
p_pattern => 'paged/',
p_method => 'GET',
p_name => '$first',
p_bind_variable_name => 'first',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'examples',
p_pattern => 'paged/',
p_method => 'GET',
p_name => '$next',
p_bind_variable_name => 'next',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'examples',
p_pattern => 'paged/',
p_method => 'GET',
p_name => '$prev',
p_bind_variable_name => 'prev',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'examples',
p_pattern => 'paged/',
p_method => 'GET',
p_name => 'read',
p_bind_variable_name => 'read',
p_source_type => 'URI',
p_param_type => 'INT',
p_access_method => 'IN',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'examples',
p_pattern => 'paged/',
p_method => 'GET',
p_name => 'skip',
p_bind_variable_name => 'skip',
p_source_type => 'URI',
p_param_type => 'INT',
p_access_method => 'IN',
p_comments => NULL);
ORDS.CREATE_ROLE(p_role_name => 'beers_client');
l_roles(1) := 'SQL Developer';
l_roles(2) := 'beers_client';
l_modules(1) := 'examples';
ORDS.DEFINE_PRIVILEGE(
p_privilege_name => 'untappd.module',
p_roles => l_roles,
p_patterns => l_patterns,
p_modules => l_modules,
p_label => 'untappd',
p_description => 'locking down our example module',
p_comments => NULL);
l_roles.DELETE;
l_modules.DELETE;
l_patterns.DELETE;
COMMIT;
END;
