Design Patterns for Oracle Cloud Integration Projects

Pattern 1: Hub-and-Spoke with OIC

Without a hub: N×(N-1) point-to-point integrations for N systems.

graph TB
    subgraph "Point-to-Point — 12 integrations for 4 systems"
        A1[Fusion ERP] <-->|1| A2[HCM]
        A1 <-->|2| A3[Custom App]
        A1 <-->|3| A4[Partner API]
        A2 <-->|4| A3
        A2 <-->|5| A4
        A3 <-->|6| A4
    end
    style A1 fill:#2d0f0f,stroke:#EF4444,color:#e2e8f0

With OIC as hub: 2N integrations — one in, one out per system.

graph LR
    ERP[Fusion ERP] <-->|2| HUB[OIC Hub]
    HCM[Oracle HCM] <-->|2| HUB
    APP[Custom App]  <-->|2| HUB
    PAR[Partner API] <-->|2| HUB

    style HUB fill:#1e3a5f,stroke:#3B82F6,color:#e2e8f0
    style ERP fill:#0f2d1f,stroke:#22C55E,color:#e2e8f0
    style HCM fill:#0f2d1f,stroke:#22C55E,color:#e2e8f0
    style APP fill:#0f2d1f,stroke:#22C55E,color:#e2e8f0
    style PAR fill:#0f2d1f,stroke:#22C55E,color:#e2e8f0

Adding a 5th system requires just 2 new integrations, not 8.

Pattern 2: Canonical Data Model

graph LR
    SRC1[Source A
CustomerNo, CustName] -->|map| CDM[(Canonical
Customer Model
customerId, name,
email, phone)]
    SRC2[Source B
AccountID, AccountTitle] -->|map| CDM
    CDM -->|map| TGT1[Target X]
    CDM -->|map| TGT2[Target Y]

    style CDM fill:#1e3a5f,stroke:#3B82F6,color:#e2e8f0

Adding Source C only requires one new mapping to the canonical model — not mappings to every existing target.

Pattern 3: Idempotent Processing

The problem: a network timeout means OIC doesn’t know if its POST was received. Retrying may create duplicates.

sequenceDiagram
    participant OIC
    participant API as Target API
    participant DB  as Track DB

    OIC->>API: POST /orders {correlationId: "ORD-2026-001", ...}
    Note over API: Network timeout!
    OIC->>API: Retry POST /orders {correlationId: "ORD-2026-001", ...}
    API->>DB: SELECT WHERE correlation_id = 'ORD-2026-001'
    DB-->>API: Already processed!
    API-->>OIC: 200 OK (returns existing order, no duplicate)

Implementation in PL/SQL:

PROCEDURE create_order_idempotent (
  p_correlation_id VARCHAR2,
  p_order_data     order_payload_t,
  p_result         OUT order_result_t
) AS
  v_existing_id NUMBER;
BEGIN
  -- Check for existing processing of this correlation ID
  SELECT order_id INTO v_existing_id
  FROM orders
  WHERE correlation_id = p_correlation_id;
  
  -- Already processed — return existing result
  p_result.order_id := v_existing_id;
  p_result.status   := 'EXISTING';
  RETURN;
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- First time — create the order
    INSERT INTO orders (correlation_id, ...)
    VALUES (p_correlation_id, ...);
    p_result.order_id := seq_orders.CURRVAL;
    p_result.status   := 'CREATED';
END;

Pattern 4: Saga — distributed transaction compensation

When a business transaction spans multiple systems and a step fails:

sequenceDiagram
    participant OIC
    participant HCM as Oracle HCM
    participant PAY as Payroll
    participant BANK as Bank API

    OIC->>HCM: Step 1: Create employee record
    HCM-->>OIC: ✓ Employee ID: 12345
    OIC->>PAY: Step 2: Create payroll assignment
    PAY-->>OIC: ✓ Payroll ID: 67890
    OIC->>BANK: Step 3: Setup bank account
    BANK-->>OIC: ✗ FAILED (invalid sort code)
    
    Note over OIC: Saga: execute compensations in reverse
    OIC->>PAY: Compensate Step 2: DELETE payroll 67890
    OIC->>HCM: Compensate Step 1: DELETE employee 12345
    OIC->>OIC: Raise alert to operations team

Each step in OIC has a corresponding compensating action in a fault handler that reverses it cleanly.