Implementing pagination to retrieve data in chunks | Oracle Integration-3
Data pagination specifically refers to the process of dividing large sets of data into manageable chunks, typically to be displayed on separate pages.
This technique is crucial for web applications, databases, and APIs to improve performance and usability.
Key Concepts of Pagination
- Page Number: Indicates which subset of the total data is currently being viewed.
- Page Size (Limit): The number of data items displayed on each page.
- Offset: The position in the overall dataset where the current page of data starts.
- Total Items: The total number of items in the dataset.
- Total Pages: The total number of pages available based on the page size and total items.
How to achieve pagination in OIC
Considering data resides in Oracle database and data is huge.
Write a PL/SQL procedure that accepts:
- Relevant request parameters such as departmentName, location, etc (Optional)
- A parameter to limit the number of records per request. Usually, the parameter name is limit(Optional).
- A parameter offset/page number that skips previous records and provides the next set of records. Usually, the parameter name is offset or page number(Required)
The response of PL/SQL procedure should contain:
- The field that indicates table has more records or not as per the sent parameters. Usually, the name of field is hasMore.
- Field that indicates the total number pages as per the limit and total number of records in the table. Total pages can be calculated using formula in PL/SQL as ceil(total_records/limit). Usually, the name of field is total_page
- Array of actual records
Consume the PL/SQL procedure in Oracle Integration and pass relevant parameters.
Sample Request and Response
Request URL:
https://oic.ocp.oraclecloud.com/ic/api/integration/v1/flows/rest/PAGINATION/1.0/data?limit=2&page=1
Sample Response
{
”total_pages" : 500,
"Products" : [ {
"Name" : "Muhammed MacIntyre",
"Desc" : "Storage & Organization",
"Amount" : 3
}, {
"Name" : "Barry French",
"Desc" : "Appliances",
"Amount" : 293
}
In the following video, We have captured all the necessary steps to show how to create a PL/SQL procedure in Oracle ATP, how to create a REST API, how to consume PL/SQL procedure, etc. Please look at it end to end.
If you found this article valuable, we would greatly appreciate your support through likes, comments, and shares. Additionally, we invite you to subscribe to our YouTube channel for more insightful videos. Don’t miss out on our latest updates – be sure to click the subscribe button to stay informed about our newest content.
Further readings:
About the Salesforce REST Adapter
Leveraging Salesforce REST adapter to push accounts to Database
OData adapter in Oracle Integration-3
Place the file in ATP from BIP using OIC
