Implement Pagination in REST Service: Integration Cloud
There are situations in which we need to fetch a large number of records from the on-premise application in the Integration Cloud. For example, there are 100k+ records resides in the on-premise database table which we need to fetch and expose them into the REST service response.
In this particular scenario, we are considering the database is installed behind the firewall. So to communicate with the on-premise application, connectivity agent is required.
Connectivity agent has some limitation in which agent can parse payload of 10MB only for a single request. Exceeding the limit may cause stack overflow error in the agent which may bring the agent down.
So to handle a large volume of data from the on-premise application, we have to keep this limitation in mind and implement the solution accordingly.
There might be multiple ways to handle such a situation. Here I’m gonna to handle this situation by implementing the pagination into the REST service which will fetch the data into the chunk.
To complete this article we should have below
- Oracle Database 12c
- Oracle Integration Cloud instance
- Connectivity agent installed
Let’s get started with step by step. Before we move forward, below must be the object which should be compiled in the Oracle Database
- A table with name test_table containing below columns
- FIRST_NAME
- LAST_NAME
- PERSONAL_EMAIL_ADDRESS
- EMPLOYEE_NUMBER
- A package with a single procedure. Below are the package specification and body script
Package specification
create or replace PACKAGE TEST_PAGINATION_PKG
AS
PROCEDURE TEST_PAGINATION_PROC( I_PAGE IN NUMBER,
I_LIMIT IN NUMBER,
I_PAGE_SIZE OUT NUMBER,
P_DATA OUT SYS_REFCURSOR);
END;
Package body
CREATE OR replace package body TEST_PAGINATION_PKG AS PROCEDURE TEST_PAGINATION_PROC(i_page IN number , i_limit IN number, i_page_size out number, p_data out sys_refcursor) AS count1 number;BEGIN
SELECT Count(*)
INTO count1
FROM test_table ;
I_PAGE_SIZE:=ceil(count1/i_limit);
OPEN p_data for
SELECT * FROM TEST_TABLE
ORDER BY employee_number offset nvl((i_page-1),0)*i_limit rows
FETCH next i_limit rows only;
END;END;
The procedure takes two input parameter:
- i_page IN: The parameter specifies which page number data we are looking for
- i_limit: The parameter specifies the number of record set we want to limit in a single run
And two output parameter:
- i_page_size: The output parameter gives the total number of pages depending on the limit we set in the i_limit input parameter. For example, if TEST_TABLE contains 150 records and we wanted to limit 10 records at a time. So, in that case, the procedure will return the value of i_page_size as 15.
- p_data: Contains the record set
Let’s see how to achieve the pagination and get records in chunks
- Create an Orchestration process in Integration Cloud Service with name Pagination_Int
- Drop a REST adapter as a Trigger End point
- Configure the below properties and click the Next button
- What do you want to call your endpoint: GetPaginatedData
- What is the endpoint’s relative resource URI?: /employees
- What action does this endpoint perform: GET
- Check the checkbox Add and review parameters for this endpoint
- Check the checkbox Configure this endpoint to receive the response
- Add a new parameter with the name page of type integer and click the Next button
- Select JSON Sample and then click the <<< inline >>> link, then enter below sample json
{
“Employees”:[
{
“FirstName”:””,
“LastName”:””,
“EmailAddress”:””
},
{
“FirstName”:””,
“LastName”:””,
“EmailAddress”:””
}
],
“PageSize”:1,
“Limit”:1,
“Page”:1
}
- Click the Done button
- Drop database adapter just above the mapper
- Enter the endpoint name as GetData and select Invoke a Stored Procedure from the operation drop down
- Select the below information and click the Next button
- Select Schema: Select the schema in which package is compiled. In our case package is compiled under apps schema so selected the apps schema
- Select Package: Select package name as TEST_PAGINATION_PKG
- Select Procedure: Select procedure as TEST_PAGINATION_PROC
- Click Done button
- Click edit icon of GetData mapper
- Map the request parameter page to I_PAGE and set the value of I_LIMIT to 100. For the instance, we would like to fetch 100 records at a time that’s the reason we set the value of I_LIMIT to 100
- Edit the GetPaginatedData mapper and map the response as below
- Drop I_PAGE_SIZE onto PageSize
- Set Limit to 100
- Drop page onto Page
- Map FirstName, LastName, and EmailAddress
- The configuration is completed. Save the integration, close and Activate.
- See the number of records into the TEST_TABLE. We have 999 records in the table.
- Let’s try to hit the service from POSTMAN
TEST-1: Set the page=1 and hit the run button. Since the total number of records in the DB is 999 and we set the limit size to 100. So the total number of pages would be 999/100 = 10 in the round figure. Notice the PageSize and Limit values
TEST-2: Set the page=2 and hit the run button
That’s the way we can achieve pagination in REST services and handle he large number of records of on-premise application.
Very useful information on Data Structures, definitely it helps us to protect our site from copied content, if you are Looking for software courses?
Hadoop Training in Chennai
Android Training in Chennai
Selenium Training in Chennai
Digital Marketing Training in Chennai
JAVA Training in Chennai
German Classes in chennai
Qtp training in Chennai
Qtp Training in Adyar
Thanks Ankur! It is indeed a good reference for implementation. Just a question, have you tried implementing this in Oracle SOA 12c onpremise setup? I will give it a try and I hope this is supported there since underlying products are same.
Hi Ankur, is there any pre-requisites for creating the package in Oracle DBCS or in OIC while creating the step? We are trying the same thing ( i.e. created same package in our DBCS system) but getting an error as below, when we create the integration
DBSPR001_GETPAGE : Failed to create stored procedure page due to: Unable to expand structured type. Unable to expand user defined type
definition. An unexpected exception occurred while trying to analyse the user defined type definition XX.XX(name masked) of parmeter P_DATA. Check to ensure that the user defined type is defined correctly in the database.
Hi Arvind,
I don't think so something special is required. But to implement this package you would be required oracle database 12c+
Regards,
Ankur
Thanks Ankur.