[OIC] How to create REST Integration with Pagination in Oracle Integration Cloud?

 

 

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>


 

 



 

 

Comments