Introduction
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.
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 APIs use two parameters and the existence of a JSON field in the response to handle pagination.
The $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.
The $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.
The $top and $skip parameters at the end of the example below tell the MS Graph API to fetch 100 rows, starting at row 101.
https://graph.microsoft.com/v1.0/users/jon@cloudnueva.com/calendars/AQMkADlmNzFhNDAxLWFjNzItNGM0MS1hNzViLTRlOTU5/events?$top=100&$skip=100
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.
"@odata.nextLink": "https://graph.microsoft.com/v1.0/users/6867a2f8-d26c-4fc1-a7c0-ffbaf2/calendars('AQMkADl')/events?%24select=id%2csubject%2cshowAs%2cisOnlineMeeting%2cbody%2cstart%2cend%2clocation&%24top=100&%24skip=100"
Plug-in Definition
Let's start by looking a the definition of the example plug-in.
Name & Type
Callbacks
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_result parameter 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.
Plug-in Code
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.
Discovery
The discovery procedure in my example is called cn_msgraph_rest_plugin_pk.discover_calendar.
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 $select to 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_result parameter 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.
Set the Fixed Page Size property of the REST Source GET operation to 100.
Capabilities
The procedure 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.
PROCEDURE capabilities_calendar
(p_plugin in apex_plugin.t_plugin,
p_result in out nocopy apex_plugin.t_web_source_capabilities) IS
BEGIN
p_result.filtering := false;
p_result.pagination := true;
p_result.order_by := false;
END capabilities_calendar;
💡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 wwv_flow_plugin_api.
Fetch
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.
The 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.
Uses the p_params.first_row parameter value passed in by APEX to calculate the value of the $skip parameter. 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 $skip and the constant fixed page size of 100 used for $top.
Calls the PL/SQL API apex_plugin_util.make_rest_request to fetch a page of data.
Inspects the response JSON_OBJECT_T to determine how many records were fetched and to see if @odata.nextLink exists in the response, indicating there are more records to fetch.
Populate the return parameter p_result to 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 Endpoint and URL Parameter 1 are automatically filled in based on the plug-in definition.
- When you click Discover, the procedure
discover_calendar is called.
- After creating the REST Source, we can see that the
$select Query String variable has been added based on code in discover_calendar procedure.
- We can also see that the Data Profile only contains the fields in the
$select parameter. This was also determined based on the code in the discover_calendar procedure.
- Finally, we can see the Fixed Page Size is set for the
GET operation. This was also determined based on the code in the discover_calendar procedure.
Next Steps
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.
Enable Caching
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.
Conclusion
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.
🔗 Read More
