Below are the few benefits of implementing pagination in your OIC REST integrations:
Improve Performance - Pagination allows you to limit the amount of data returned in each
request, which can significantly improve performance, especially when
dealing with large datasets. By retrieving data in smaller chunks, you
reduce the load on both the client and server sides, making the
integration more efficient.
Resource Management - Large datasets can consume a
significant amount of system resources, such as memory and network
bandwidth. Pagination helps in managing these resources more effectively
by fetching only the required subset of data at a time, thus preventing
resource exhaustion.
Avoiding Timeout Issues: Retrieving large amounts of
data in a single request can lead to timeout issues, especially in
environments with strict request/response time limits. Pagination
ensures that each request only fetches a manageable amount of data,
reducing the likelihood of timeouts.
Scalability: Pagination allows integrations to scale
more effectively. As the dataset grows, pagination ensures that the
integration remains responsive and efficient by fetching data in
smaller, manageable chunks.
Reduced Data Transfer Costs: In scenarios where data
transfer costs are a concern (e.g., in cloud environments where outbound
data transfer is billed), pagination helps reduce costs by limiting the
amount of data transferred in each request.
This post will show how to create REST Integration with Pagination in Oracle Integration Cloud.
Suppose you integration is returning the data in following JSON format:
{
"items": [
{
"id": 1,
"name": "Leanne Graham",
"username": "Bret",
"email": "Sincere@april.biz",
"website": "hildegard.org"
}
], "status": "Success",
"message": "Data fetched successfully"
}
Now, to implement pagination you have to add few additional fields in the response payload:
{
"count":3,
"hasMore":true,
"limit":10,
"offset":0,
"items": [
{
"id": 1,
"name": "Leanne Graham",
"username": "Bret",
"email": "Sincere@april.biz",
"website": "hildegard.org"
}
],
"status": "Success",
"message": "Data fetched successfully"
}
Also, add two query parameters in REST Trigger "limit" and "offset"
Suppose, previously you had used DB adapter with the below query for your integration:
SELECT
id
,name
,username
,email
,website
FROM
employees
Now just write the below procedure which will return the data and other attributes.
CREATE OR REPLACE PACKAGE BODY EMP_DATA_PKG
/* This Procedure is invoked from OIC returns the paginated data*/
PROCEDURE get_data(
p_in_limit IN NUMBER DEFAULT 0,
p_in_offset IN NUMBER DEFAULT 25,
p_out_has_more OUT NUMBER,
p_out_count OUT NUMBER,
p_out_status OUT VARCHAR2,
p_out_message OUT VARCHAR2,
p_out_data OUT SYS_REFCURSOR
)
AS
BEGIN
--Get total row count
SELECT COUNT(*) INTO p_out_count
FROM employees;
--Get data with pagination
OPEN p_out_data FOR
SELECT
id
,name
,username
,email
,website
FROM
employees
WHERE 1=1
ORDER BY id
OFFSET p_in_offset ROWS
FETCH NEXT p_in_limit ROWS ONLY
;
IF (p_in_offset + p_in_limit)>= p_out_count
THEN
p_out_has_more :=0;
p_out_status :='Success';
p_out_message :='Data fetched successfully';
ELSE
p_out_has_more :=1;
p_out_status :='Warning';
p_out_message :='No data found';
END IF;
EXCEPTION
WHEN OTHERS THEN
p_out_status :='Error';
p_out_message :='Exception: '||SQLERRM;
END get_data;
END EMP_DATA_PKG;
/
So, by calling this procedure in OIC you will be able to get the paginated data.
Below is the mapper file for mapping DB Adapter reponse to the REST response. Here we have used XSLT to get the field hasMore of response payload:
<xsl:template match="/" xml:id="id_11">
<nstrgmpr:executeResponse xml:id="id_12">
<nstrgdfl:response-wrapper>
<xsl:choose>
<xsl:when test="$GetEmpDataFromDB/nsmpr0:OutputParameters/nsmpr0:P_OUT_HAS_MORE = 1">
<nstrgdfl:hasMore>
<xsl:value-of select="true()"/>
</nstrgdfl:hasMore>
</xsl:when>
<xsl:otherwise>
<nstrgdfl:hasMore>
<xsl:value-of select="false()"/>
</nstrgdfl:hasMore>
</xsl:otherwise>
</xsl:choose>
<nstrgdfl:totalCount>
<xsl:value-of select="$GetEmpDataFromDB/nsmpr0:OutputParameters/nsmpr0:P_OUT_COUNT"/>
</nstrgdfl:totalCount>
<xsl:for-each select="$GetEmpDataFromDB/nsmpr0:OutputParameters/nsmpr0:P_OUT_DATA/nsmpr0:Row">
<nstrgdfl:items>
<nstrgdfl:id>
<xsl:value-of select="nsmpr0:Column[@name='ID']"/>
</nstrgdfl:id>
<nstrgdfl:name>
<xsl:value-of select="nsmpr0:Column[@name='NAME']"/>
</nstrgdfl:name>
<nstrgdfl:username>
<xsl:value-of select="nsmpr0:Column[@name='USERNAME']"/>
</nstrgdfl:username>
<nstrgdfl:email>
<xsl:value-of select="nsmpr0:Column[@name='EMAIL']"/>
</nstrgdfl:email>
<nstrgdfl:website>
<xsl:value-of select="nsmpr0:Column[@name='WEBSITE']"/>
</nstrgdfl:website>
</nstrgdfl:items>
</xsl:for-each>
<nstrgdfl:Status>
<xsl:value-of select="$GetEmpDataFromDB/nsmpr0:OutputParameters/nsmpr0:P_OUT_STATUS"/>
</nstrgdfl:Status>
<nstrgdfl:Message>
<xsl:value-of select="$GetEmpDataFromDB/nsmpr0:OutputParameters/nsmpr0:P_OUT_MESSAGE"/>
</nstrgdfl:Message>
</nstrgdfl:response-wrapper>
</nstrgmpr:executeResponse>
</xsl:template>
Default values for offset and limit are wrong.
ReplyDelete