# Master APEX REST Source Pagination with REST Source Connector Plug-Ins

# 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](https://docs.oracle.com/en/database/oracle/apex/22.2/htmdb/creating-a-REST-data-source.html) support three common pagination schemes out of the box.

![Oracle APEX REST Source Pagination](https://cdn.hashnode.com/res/hashnode/image/upload/v1681916190697/0e6c29c0-c1d2-4604-a6e1-dc464782410d.png align="center")

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](https://blog.cloudnueva.com/apex-office-365-calendar-integration) 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](https://github.com/oracle/apex/tree/22.2/plugins/rest-source/fixed-page-size) as a starting point for my example plug-in.

📥 Before reading further, I encourage you to [download](https://github.com/jon-dixon/cn_blog_demos/tree/main/msgraph_calendar_plugin) 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](https://learn.microsoft.com/en-us/graph/query-parameters?tabs=http) 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.

```plaintext
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.

```json
"@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

![Oracle APEX REST Source Plugin Definition](https://cdn.hashnode.com/res/hashnode/image/upload/v1681917508959/e4fcd7cd-71ae-41c0-86bd-fe7b0aadfef0.png align="center")

## Callbacks

![Oracle APEX REST Source Plugin Definition Callbacks](https://cdn.hashnode.com/res/hashnode/image/upload/v1681924721183/04edbc5f-8f76-4a81-b8ce-b1c6c4044eb7.png align="center")

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](https://docs.oracle.com/en/database/oracle/apex/22.2/aeapi/EXECUTE_REST_SOURCE-procedure.html#GUID-2DB748DC-E5B7-4AF8-B64A-6716F8A5E447) 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.
    

![Oracle APEX REST Source Plugin Definition](https://cdn.hashnode.com/res/hashnode/image/upload/v1681918930352/b2e3da9f-e37a-4d34-bb16-3928c3a41513.png align="center")

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](https://github.com/jon-dixon/cn_blog_demos/tree/main/msgraph_calendar_plugin). 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.

![Oracle APEX REST Create - Discovery](https://cdn.hashnode.com/res/hashnode/image/upload/v1681924920088/03b19741-3306-4555-89ec-04336ce6fb49.png align="center")

In the procedure `discover_calendar` I perform three tasks:

1. 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.
    
2. 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.
    
3. 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.

```sql
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:

1. Calls the [apex\_plugin\_util.get\_web\_source\_operation](https://docs.oracle.com/en/database/oracle/apex/22.2/aeapi/GET_WEB_SOURCE_OPERATION-Function.html#GUID-653B2B87-5AEE-444F-9C97-04220456C761) to get details of the REST Source, parameters passed in from APEX, etc.
    
2. 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.
    
3. If 'fetch all rows' was invoked, loop through fetching all pages of data from the MS Graph API.
    
4. Generates the query string parameters, including the calculated value for `$skip` and the constant fixed page size of 100 used for `$top`.
    
5. Calls the PL/SQL API [apex\_plugin\_util.make\_rest\_request](https://docs.oracle.com/en/database/oracle/apex/22.2/aeapi/MAKE_REST_REQUEST-Procedure-Signature-1.html#GUID-8E618B80-FDE9-475C-BC0C-B7C985FCECFF) to fetch a page of data.
    
6. 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.
    
7. 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.

![Create APEX Res Source from Plug-In 1](https://cdn.hashnode.com/res/hashnode/image/upload/v1681947278242/00f0d4db-4356-428f-94da-f3a60b0cd5f4.png align="center")

![Create APEX Res Source from Plug-In 2](https://cdn.hashnode.com/res/hashnode/image/upload/v1681947341125/8676e558-c308-44e1-8a12-1c53c24244e2.png align="center")

* 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.
    

![Create APEX Res Source from Plug-In 3](https://cdn.hashnode.com/res/hashnode/image/upload/v1681947436966/b1c347ba-7a65-4abb-a8cb-0a5bddcd5446.png align="center")

![Create APEX Res Source from Plug-In 4](https://cdn.hashnode.com/res/hashnode/image/upload/v1681947494866/c2c0a9cc-e62b-434e-9541-1c84c91f8181.png align="center")

* When you click Discover, the procedure `discover_calendar` is called.
    

![Create APEX Res Source from Plug-In 5](https://cdn.hashnode.com/res/hashnode/image/upload/v1681947559832/49de88cd-a201-4b3b-b707-fdad31f7ab73.png align="center")

![Create APEX Res Source from Plug-In 6](https://cdn.hashnode.com/res/hashnode/image/upload/v1681947601089/fb2519dc-4955-4aa1-9518-941bb0f9c44a.png align="center")

* After creating the REST Source, we can see that the `$select` Query String variable has been added based on code in `discover_calendar` procedure.
    

![Create APEX Res Source from Plug-In 7](https://cdn.hashnode.com/res/hashnode/image/upload/v1681947739508/c0727035-8ffd-4480-ba91-947d365df6a9.png align="center")

* 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.
    

![Create APEX Rest Source from Plug-In 8](https://cdn.hashnode.com/res/hashnode/image/upload/v1681947957884/959ba9d1-2598-4ba2-856a-5b48e4a3aca7.png align="center")

* 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.

![APEX Rest Source caching](https://cdn.hashnode.com/res/hashnode/image/upload/v1681948168910/506e851a-9c6f-4202-b15b-ea84d8409a2b.png align="center")

# 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

* 🩳 [APEX Shorts](https://blog.cloudnueva.com/series/apex-shorts)
    
* 📝 [APEX Posts](https://blog.cloudnueva.com/tag/orclapex)