Master APEX REST Source Pagination with REST Source Connector Plug-Ins
When you deal with REST APIs, sooner or later, you must also deal with pagination. As of version 23.1, APEX REST Data Sources support three common pagination schemes out of the box.
Unfortunately, there are hundreds of pagination schemes in circulation. What do we do when working with a REST API that does not fit these three pagination types?
Luckily the APEX development team thought of this and has given us REST Source Connector Plug-Ins.
This post will show you how to build a simple REST Source Connector Plug-In to handle pagination for the MS Graph Calendar API. Please read my previous post on the MS Graph Calendar API for more on working with this API in APEX.
💡 This plugin can be adapted for use in all MS Graph APIs with minor changes.
I used the Sample Rest Source Pagination plug-in as a starting point for my example plug-in.
📥 Before reading further, I encourage you to download my example PL/SQL package and plug-in, discussed throughout this post.
What is Pagination
Pagination is a technique used in RESTful web services to limit the number of records returned in a response. It improves performance and reduces network traffic by breaking down large data sets into smaller chunks called pages.
Pagination divides the data into pages and returns only a subset of records on each page. The client can then request additional pages until all records have been retrieved.
MS Graph Pagination
MS Graph APIs use two parameters and the existence of a JSON field in the response to handle pagination.
$top parameter tells the MS Graph API only to return a certain number of rows at a time. This can be thought of as the page size. Each MS Graph API has its own limit to the maximum number of rows you can fetch at a time.
$skip parameter tells the MS Graph API to skip the first X rows. When used in conjunction with
$top you can determine which page you are on.
$skip parameters at the end of the example below tell the MS Graph API to fetch 100 rows, starting at row 101.
When an MS Graph API fetches a page of data, it includes a field called
@odata.nextLink in the response if there is more data to fetch. If there is no more data to fetch, this field will not be included. We can use this field's existence (or otherwise) to determine if we have reached the final page.
Let's start by looking a the definition of the example plug-in.
Name & Type
The Callbacks section identifies code called at specific points during plug-in processing.
REST Source Capabilities Procedure- APEX calls this procedure to determine what features your plug-in supports. Features include pagination, filtering, and order by.
REST Source Fetch Procedure- This procedure is called by APEX when it needs to fetch data from the REST Source. Whatever data your output in the
p_resultparameter is returned to the APEX component (e.g., Interactive Report). This is where the pagination logic comes into play.
REST Source DML Procedure- This code is called when APEX attempts to perform DML operations with your REST Source.
REST Source Execute Procedure- This code implements the execute operation, which is invoked when APEX_EXEC.EXECUTE_REST_SOURCE is called.
REST Source Discover Procedure- This code is called during the discovery phase of creating a REST Source based on a plug-in. You can use this to return a specific response to the discover step. In my example, I will use this code to restrict the columns in the REST Source data profile.
The Standard Attribute additional Meta Data section allows you to specify a default Endpoint for the REST Source. This pre-populates the EndPoint URL for the developer when creating a REST Source based on the plug-in.
As with all APEX plug-ins, you should maintain plug-in PL/SQL code in a PL/SQL package. In my example, I have a package called
cn_msgraph_rest_plugin_pk. You can access the complete package and the Plug-In here. I recommend you download the package body before proceeding.
The discovery procedure in my example is called
This procedure is called when a developer creates a REST Source using the plug-in and clicks the 'Discover' button.
In the procedure
discover_calendar I perform three tasks:
Add the MS Graph parameter
$selectto the REST Source Definition. This parameter allows me to select specific columns to fetch from the MS Graph Calendar API. APEX then uses this limited set of columns to create the Data Profile for the REST source. Any REST API parameters returned in the
p_resultparameter are included in the new REST Source definition.
Call the MS Graph Calendar API to fetch a sample payload, which is used to display to the end user and create the initial REST Source Data Profile.
Fixed Page Sizeproperty of the REST Source
GEToperation to 100.
cn_msgraph_rest_plugin_pk.capabilities_calendar tells APEX what features the plug-in supports. This is achieved by populating a PL/SQL record type called apex_plugin.t_web_source_capabilities. In my example, I am only supporting pagination.
(p_plugin in apex_plugin.t_plugin,
p_result in out nocopy apex_plugin.t_web_source_capabilities) IS
p_result.filtering := false;
p_result.pagination := true;
p_result.order_by := false;
💡I recommend you get hold of a copy of the package specification for the package
wwv_flow_plugin_api. This contains definitions for the PL/SQL record and table types used throughout by APEX REST Source Connector Plug-Ins.
apex_plugin is a synonym for
The callback in my example is the procedure
cn_msgraph_rest_plugin_pk.fetch_calendar. This is where all the magic happens. When an APEX component performs a fetch using a REST Source based on a REST Source Connector Plug-in, it calls this procedure to fetch the data. This is where we tell APEX how to page through the REST Service results to fetch all the records.
fetch_calendar procedure does the following:
Calls the apex_plugin_util.get_web_source_operation to get details of the REST Source, parameters passed in from APEX, etc.
p_params.first_rowparameter value passed in by APEX to calculate the value of the
$skipparameter. For example, if APEX requests records starting at row 40, we must skip 39 records when calling the MS Graph API.
If 'fetch all rows' was invoked, loop through fetching all pages of data from the MS Graph API.
Generates the query string parameters, including the calculated value for
$skipand the constant fixed page size of 100 used for
Calls the PL/SQL API apex_plugin_util.make_rest_request to fetch a page of data.
Inspects the response
JSON_OBJECT_Tto determine how many records were fetched and to see if
@odata.nextLinkexists in the response, indicating there are more records to fetch.
Populate the return parameter
p_resultto tell APEX how many records were fetched and if more records need to be fetched.
Creating a REST Source
In this section, we will create a REST Source using the example plug-in.
- After selecting the example Plug-in as the
REST Data Source Type, the
URL Parameter 1are automatically filled in based on the plug-in definition.
- When you click Discover, the procedure
- After creating the REST Source, we can see that the
$selectQuery String variable has been added based on code in
- We can also see that the Data Profile only contains the fields in the
$selectparameter. This was also determined based on the code in the
- Finally, we can see the Fixed Page Size is set for the
GEToperation. This was also determined based on the code in the
Now that the REST Source has been created, it can be used in an APEX component, such as a Calendar or Interactive Report region. As users navigate between pages in the APEX component, APEX will call the
fetch_calendar procedure to fetch rows required for the region.
If you don't need real-time data, I strongly encourage you to turn on REST Source Caching. When turned on, APEX will cache REST responses locally, which can improve performance dramatically. This is especially true if you have to fetch multiple pages of data to get the entire data set.
At first, I had difficulty understanding REST Source Connector Plug-Ins and how they interact with APEX and REST Data sources. However, it was worth figuring them out because I am now confident I can handle just about any pagination scheme when using APEX REST Data Sources. I hope this blog post makes getting started with REST Source Connector Plug-ins easier for you.