Handling long-running BI Publisher (BIP) reports with huge data in Oracle Integration Cloud

 Handling long-running BI Publisher (BIP) reports with huge data in Oracle Integration Cloud (OIC) is a very common real-time challenge. Here are the best-practice approaches used in production environments.

---

✅ 1. Use Asynchronous Report Execution (Most Important)

Never run large reports in synchronous mode.

❌ Problem

Synchronous calls timeout in OIC (typically 5–10 mins).

✅ Solution

Use asynchronous execution:

Flow:

Submit Report → Get Job ID → Poll Status → Download Output

Steps:

1️⃣ Call runReport with:

<runReport>
  <reportRequest>
    <attributeFormat>xml</attributeFormat>
    <byPassCache>true</byPassCache>
    <flattenXML>false</flattenXML>
    <sizeOfDataChunkDownload>-1</sizeOfDataChunkDownload>
  </reportRequest>
</runReport>

2️⃣ Capture jobId

3️⃣ Poll using getReportStatus

4️⃣ Download using getOutput

✅ This avoids timeout.

---

✅ 2. Use Pagination / Chunking (Split Data)

Never fetch millions of records at once.

Best Practice:

Split report by:

Parameter	Example
Date	From–To Date
Batch	Batch ID
Org	BU
Range	ID ranges

Example:

From Date = Today-1
To Date = Today

Then loop in OIC.

✅ Prevents memory overflow.

---

✅ 3. Optimize BI Publisher SQL Query

Many performance issues come from bad SQL.

Do This:

✔ Use proper indexes
✔ Avoid SELECT *
✔ Use filters
✔ Avoid correlated subqueries
✔ Use EXISTS instead of IN
✔ Use materialized views

Example:

SELECT order_id, customer_name
FROM oe_orders
WHERE last_update_date >= :p_from_date

❌ Avoid:

SELECT * FROM oe_orders

---

✅ 4. Enable Data Chunk Download

For very large XML/CSV files.

Set:

<sizeOfDataChunkDownload>10485760</sizeOfDataChunkDownload>

= 10 MB chunks

OR

-1 (entire file)

Recommended: Use chunking.

---

✅ 5. Use File Server / Stage File Instead of Memory

Never hold huge payload in OIC memory.

Best Pattern:

BIP → OIC → Stage File → Process → FTP/S3

Use:

✔ Stage File Read/Write
✔ OIC Lookups for tracking
✔ Streaming

Example:

Stage File → Write → Process Line-by-Line

---

✅ 6. Use Schedule + Callback Pattern (Advanced)

For extremely heavy reports.

Pattern:

1. Schedule report in Fusion

2. Send output to UCM/FTP

3. OIC picks file

4. Process async

✅ Zero timeout risk.

---

✅ 7. Increase OIC Tracking & Fault Tolerance

Disable payload tracking for huge data:

In Integration Insight:

Disable Tracking = TRUE

Or use business identifiers only.

✅ Improves performance.

---

✅ 8. Use CSV Instead of XML (If Possible)

XML = Heavy
CSV = Lightweight

Preferred:

Output Format = CSV

Benefits:

• Faster

• Smaller size

• Easier parsing

---

✅ 9. Parallel Processing (When Allowed)

If business allows:

Split by:

• Region

• BU

• Date

Run multiple integrations.

Example:

North + South + East + West

Run in parallel.

---

✅ 10. Timeout & Retry Handling

Implement retry logic:

If Status = Running → Wait → Retry
If Failed → Alert

Use OIC Scheduler + Scope Fault.

---

📌 Recommended Architecture (Real-Time)

OIC Scheduler
     ↓
Submit BIP Report (Async)
     ↓
Poll Status
     ↓
Download in Chunks
     ↓
Write to Stage File
     ↓
Process / FTP / API

---

⚠️ Common Mistakes (Avoid These)

❌ Calling runReport synchronously
❌ Fetching millions of rows
❌ Holding payload in memory
❌ No filters
❌ No retries
❌ No staging

---

🎯 Real Project Example

Scenario:

Daily AR invoice report (5M rows)

Solution:

✔ Split by date (1 day)
✔ CSV format
✔ Async mode
✔ Stage file
✔ FTP delivery

Result:
⏱ Reduced time: 2 hrs → 15 mins
❌ Zero failures