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:
Steps:
1️⃣ Call runReport with:
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:
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:
❌ Avoid:
✅ 4. Enable Data Chunk Download
For very large XML/CSV files.
Set:
= 10 MB chunks
OR
Recommended: Use chunking.
✅ 5. Use File Server / Stage File Instead of Memory
Never hold huge payload in OIC memory.
Best Pattern:
Use:
✔ Stage File Read/Write
✔ OIC Lookups for tracking
✔ Streaming
Example:
✅ 6. Use Schedule + Callback Pattern (Advanced)
For extremely heavy reports.
Pattern:
-
Schedule report in Fusion
-
Send output to UCM/FTP
-
OIC picks file
-
Process async
✅ Zero timeout risk.
✅ 7. Increase OIC Tracking & Fault Tolerance
Disable payload tracking for huge data:
In Integration Insight:
Or use business identifiers only.
✅ Improves performance.
✅ 8. Use CSV Instead of XML (If Possible)
XML = Heavy
CSV = Lightweight
Preferred:
Benefits:
-
Faster
-
Smaller size
-
Easier parsing
✅ 9. Parallel Processing (When Allowed)
If business allows:
Split by:
-
Region
-
BU
-
Date
Run multiple integrations.
Example:
Run in parallel.
✅ 10. Timeout & Retry Handling
Implement retry logic:
Use OIC Scheduler + Scope Fault.
📌 Recommended Architecture (Real-Time)
⚠️ 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
