Powering Scalable Outbound Item Integrations from Oracle PDH to Oracle ATP Using OIC

In today's dynamic cloud-driven landscape, integrating core product data between Oracle SaaS and autonomous databases is essential for building scalable enterprise data hubs. One of our recent success stories involved designing and implementing a robust, scalable outbound integration from Oracle Product Hub Cloud (PDH) to Oracle Autonomous Transaction Processing (ATP) using Oracle Integration Cloud (OIC).

The Business Challenge

Our customer needed to extract and synchronize high-volume item master data—including **Item details, Item Relationships, Item Categories, and Item Structures—from Oracle PDH to a custom application layer built on Oracle ATP.

Given the volume and hierarchical nature of item data, a direct real-time query approach was not optimal. Instead, we architected a high-throughput batch integrationusing Oracle’s BIP reports.

The Solution: OIC at the Core

We leveraged the strength and flexibility of Oracle Integration Cloud (OIC) to orchestrate the integration in a scheduled fashion, ensuring accuracy, performance, and resilience.

Step 1: Data Extraction via Oracle BIP Report

We designed a single, unified BIP (BI Publisher) Report in Oracle Fusion SaaS to extract:

• Item Master details
• Item Category Assignments
• Item Relationships
• Item Structures

This BIP report generates a large output file (CSV) which becomes the input to our OIC integration.

OIC Scheduled Integration – Designed for High Volume

We created a Scheduled Orchestration Integration in OIC with the following core components:

Invoke BIP Report

• Dynamically trigger BIP report and retrieve the report output URL.\
• Supports BIP authentication and payload control.

Read File in Segments – Using ‘Stage File’ + Looping Logic

Given the large size of the BIP output file, we avoided loading it fully into memory.

OIC’s “Stage File” action lets us:

• Read files in segments or chunks
• Process them in a looped fashion, ensuring scalability and fault-tolerance
• This avoids timeouts and memory constraints typical in large file processing.

Parse & Transform

• Using XSLT mappings and switch logic, we parsed each record segment into normalized item rows.
• This included lookups, cleansing, and key mapping as needed.

Insert to ATP – Using OIC Database Adapter

Leveraged OIC’s Oracle Database Adapter to perform:

Conditional upserts based on item identifiers

Key OIC Capabilities Demonstrated

• Stage File                          Process large BIP output file in manageable segments                 
• Read File in Segments      Controlled looping to ensure high volume processing without timeouts 
• Database Adapter             Seamless connectivity to Oracle ATP with insert/upsert support       
• Scheduled Integrations     Automate data sync based on business timing requirements             
• SOAP/REST Support       Trigger and fetch reports securely from Oracle SaaS                  

Outcome

• Improved performance by chunking data intelligently
• High fault tolerance restarts can resume from last successful segment
• Secure, scalable data sync from Oracle PDH SaaS to Oracle ATP
• Easy to extend and monitor with built-in logging and error handling