Tuesday, June 18, 2024

OIC - How to extract large data from Oracle Fusion using REST APIs | How to overcome REST APIs limitation which can return only 499 records in a single request

Usecase: Here, we will get Rest data in 500 records size chunks using limit and offset.


Oracle REST APIs can return only 499 (<500) records in a single request. But in Fusion, there may be more than 499+ records so we need to fetch all records


We need to make use of Oracle REST APIs parameters like offset and limit and call REST API in the loop until hasMore attribute in the response is false.

Sample Request URL to test rest: 


Operation: GET


    "items": [
            "InvoiceNumber": "test23",
            "InvoiceCurrency": "USD"
    "count": 1,
    "hasMore": true,
    "limit": 1,
    "offset": 0,
    "links": [
            "rel": "self",
            "href": "https://servername.fa.us2.oraclecloud.com:443/fscmRestApi/resources/",
            "name": "invoices",
            "kind": "collection"

Implementaion steps:

  1. Create a new Scheduled integration and assign all the required variables offset=0, limit= 500, hasMore="true", onlyData="true", links="self", fields="InvoiceNumber,InvoiceCurrency"
  2. Take a stage and write the headers
  3. Take a while and loop till hasMore != "false"
  4. Configure rest endpoint to get the invoice data with the qurey params
    1. URI: fscmRestApi/resources/latest/invoices
    2. Query params: fields,onlyData,links,limit, offset
    3. Json response 
    4. Map the fields
  5. Take a stage and write invoices
  6. Take a assign and update: 
    1. hasMore= from the inboice response hasMore, 
    2. offset = $offset + 500
  7. Take a ftp adapter and configure to write final response.

Detailed steps with screenshots:

Integration flow:

Assign globals

Write headers


Get rest call

Write to stage.

Update variable

Write to ftp directory

Generated file

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...