How to overcome BIP Report Payload limitation for data larger than 10 MB

 This blog covers the size payload limitation while calling BIP report from Oracle Integration cloud. When you’re calling BIP report via Public Report Service, you will not be able to download Payload more than 10 MB of size. Click Here 

Let’s understand the above with a Business Scenario: 

 You have a large government council, and they are posting data from different modules like GL, AP, AR, Payroll to SLA (Subledger Accounting). Millions of journal lines are being posted every day and some of the information including the Chart of Accounts must be sent across Third-party supplier for their reconciliation. As a part of the activity, we need to fetch all the million lines from SLA tables to supply back to Third-party Systems. In this scenario, your data volume is far higher than 10 MB and you will often see payload limitation errors. 

There are couple of options which you can use to overcome this limitation: 

1. Create ESS Job which will burst your output to SFTP location and read data from SFTP in segment from your Integration. 

2. Use Content Manager (UCM) as a delivery option for your BIP output and read data in chunk from UCM using ReportFileID. 

In this article we’re going to use second option as it’s quite easy to implement and manage. 

Prerequisite: 

1. Please refer my earlier blog which will describe “How to call BIP report in Oracle Integration Cloud”. 

1. Update BIP Report Mapping 

Update BIP Report Mapping and provide sizeOfDataChunkDownload as 0 instead of -1. 

If you’re passing sizeOfDataChunkDownload as -1 it will return all the data back to the client, to avoid this scenario pass 0 instead of -1 it will generate the output file in UCM server and will return the report ID to fetch data in chunks. 

callBIPReport response will return Report File ID using which you can download data in chunk from UCM. 

Assign reportFileID to some variable for future use and create two more variable  

1. BeginIndex with value 0 

2. ReportDownloadDataChunk with value ‘’(blank String). 

3. reportFileID : callBIPReport à runReportResponse à reportFileID 

Now you have assigned all the parameters with appropriate value.  

In next step we will call downloadReportDataChunk operation from PublicReportWSSService to download data in chunk. 

2. Call downloadReportDataChunk Operation 

We need to call this operation until there is no data remaining to fetch. To implement this logic, we need to call this operation into the while loop, which will execute until begin index is -1. 

• Implement While loop in Integration 

Once you click on Create button, it will open mapping page where you need to provide exit criteria for While loop in New Condition section. 

Condition : BeginIndex != -1 

Note: You have defined BeginIndex in Step 1. 

Click on Validate and Save your Integration. 

• Call downloadReportDataChunk operation in Loop. 

Invoke BIP Report Connection created in “How to call BIP report in Oracle Integration Cloud” Step 1. 

• It will open on wizard. Enter the operation name, select Next.  From the next screen select Operation : downloadReportDataChunk, select Next. From the Next screen Configure Headers (Non-Mandatory) and finish the wizard. 

• Open the mapper and map the request parameters. 

• fileID: Report File ID generated by runReport operation. 

• BeginIndex: It defines from where to start reading. Ideally it should be from 0. 
• Size: It defines the size of data chunk to be downloaded in single call. It takes value in Kilo Bytes. 

• downloadReportDataChunk operation will return reportDataOffSet and reportDataChunk. 

reportDataOffSet : It will define the last index fetched from BIP report data 

reportDataChunk: It fetches the report data in encoded format 

• Assign Report Data and Report Offset to variable. 

While assigning Report Data chunk use concat function to append the data in ReportDownloadDataChunk parameter created in Step 1 and set Begin Index as reportDataOffSet. 

• Every time While loop iterate it will check for BeginIndex, if there is no data to be fetch, downloadReportDataChunk operation will return reportDataOffSet as -1 which will satisfy loop condition and it will end the iteration. 

• Now you have a final data in ReportDownloadDataChunk variable in encoded format. To decode the, create assign activity, add variable and use decodeBase64 function. 

• Click validate and save your integration. Now you can activate and test your integration. 
  This is how your Integration flow will look alike.