Dynamic Wait in OIC Gen3 When the Wait Action Isn’t Truly Runtime-Parameterized: A BI Publisher (BIP) + PL/SQL Pattern

How to implement a configurable delay by invoking a parameterized BI Publisher report from Oracle Integration Cloud

Executive summary

Teams often need a “dynamic wait” in Oracle Integration Cloud (OIC) Gen3 — for example, to slow down calls to a constrained API, to debounce eventual consistency in Oracle Fusion, or to enforce adaptive throttling between steps. Although OIC provides a Wait action, many projects still struggle to parameterize the wait time directly from runtime payload/context in a clean way. A pragmatic workaround is to externalize the delay into Oracle Fusion BI Publisher (BIP) by creating a parameterized report that executes a PL/SQL “sleep/wait” function, then calling that report from OIC with a wait parameter. This achieves truly dynamic wait durations across integrations while keeping the orchestration simple.

The problem: “dynamic wait” needed, but hard to drive purely at runtime

OIC’s Wait action can delay processing for a specified time period and can be configured using literal values (seconds) or an XPath expression. Oracle also documents that XPath expressions can be configured with property values that can be updated after activation. However, teams frequently need to compute wait durations from runtime conditions (payload, external rate limits, per-partner rules, etc.) and apply them dynamically without relying on manual property updates or redeployments.

This need is common enough that it has been raised in the community as a request for a “dynamic wait time” capability in OIC.

Solution pattern: a “Delay-as-a-Service” implemented in Fusion BIP (PL/SQL)

The pattern is simple: implement the delay in Oracle Fusion BI Publisher (BIP) using a PL/SQL dataset and a report parameter (for example, WAIT_SECONDS). OIC invokes the BIP report through the BI Publisher SOAP service (runReport) and passes the wait parameter. The report executes PL/SQL to wait/sleep for the requested duration, returns a small response, and OIC continues processing once the invoke completes.

Why this works well:

• BIP reports are naturally parameterized, so the wait duration becomes a true runtime input.
• OIC can invoke BIP services synchronously; the report response returns as base64, which OIC can handle (or ignore if the output is trivial).
• The same BIP report can be reused across many integrations to standardize throttling and pacing.

High-level architecture

OIC Integration (Gen3)
→ Compute dynamic wait (seconds)
→ Invoke BIP “WAIT” report (ExternalReportWSSService / runReport) with WAIT_SECONDS
→ BIP report runs PL/SQL sleep/wait for WAIT_SECONDS
→ BIP returns small payload (e.g., “OK” + elapsed seconds)
→ OIC continues next actions

Step-by-step implementation

Step 1 — Create a BI Publisher data model with a PL/SQL “wait” dataset

Create a BIP Data Model with a PL/SQL dataset. The dataset should accept a parameter (for example, P_WAIT_SECONDS) and execute a server-side wait. Keep it safe by enforcing a maximum wait to avoid runaway calls.

Example PL/SQL dataset pseudo-pattern (illustrative):

DECLARE
l_wait NUMBER := :P_WAIT_SECONDS;
BEGIN
l_wait := LEAST(GREATEST(l_wait, 0), 600); — cap at 10 minutes
— Use a DB sleep mechanism available in your environment
— e.g., DBMS_LOCK.SLEEP(l_wait);
OPEN :XDO_CURSOR FOR
SELECT l_wait AS waited_seconds, SYSTIMESTAMP AS server_time FROM dual;
END;

Notes:

• Cap the wait time (e.g., 600 seconds) to protect the environment.
• Return a trivial dataset so the report runs quickly before/after the sleep.
• Avoid long waits that exceed OIC invoke timeouts or operational SLAs.

Step 2 — Create a simple BIP report that exposes the wait parameter

Create a report based on the data model and expose the parameter as a standard report parameter. The output can be CSV/XML/Text with a single row; it is not the output that matters, but the delay side-effect.

Step 3 — Invoke the report from OIC using BI Publisher SOAP services

Invoke the report using the BI Publisher SOAP endpoint (for example, ExternalReportWSSService runReport). Oracle documents that BI Publisher report services can be executed synchronously and the response is returned as a base64-encoded string.

Typical request fields include:

• reportAbsolutePath: the full path to the report (for example, /Custom/XX/OIC_WAIT_REPORT.xdo).
• parameterNameValues: name/value pairs for parameters (WAIT_SECONDS).
• attributeFormat: CSV/XML (anything lightweight).
• sizeOfDataChunkDownload: -1 to retrieve in one response (if supported for your report size).

OIC handling notes:

• If you need the output, decode the base64 to a reference using decodeBase64ToReference and read it via Stage File.
• If you don’t need output, you can ignore it and simply treat the call as a “delay step”.
• Prefer short responses, because synchronous BI calls can add latency and have payload size considerations.

Design considerations (important in production)

Timeouts and limits

• OIC Wait time cannot exceed the total running time of the integration; similarly, an external delay call must respect overall runtime limits.
• Keep wait durations small and bounded; long sleeps consume resources and reduce throughput.
• If you need multi-hour pacing, redesign using scheduling, asynchronous patterns, or external queues instead of “sleep”.

Security and access

• Keep the BIP report in a secured folder and restrict execution permissions.

Observability

• Return waited_seconds and server_time in the report output (optional) and log it for proof of delay.
• Track how often delays are invoked; frequent waits can indicate upstream throttling needs or design changes.

Alternatives to consider

Before adopting the BIP/PLSQL delay pattern everywhere, evaluate whether native options fit. Oracle documents that the Wait action supports literal seconds and XPath expressions, and the XPath path can be tied to integration properties that are updated post-activation.

If you truly need per-message dynamic control, the BIP/PLSQL delay service is a pragmatic workaround that we have successfully adopted.

References (public documentation)

1. Oracle Integration 3: Delay integration execution for a specified time period with a Wait action (literal seconds, XPath, property-driven runtime updates).
2. Oracle Integration SOAP Adapter: Call Oracle Fusion Applications BI Publisher report services (runReport, base64 response, synchronous considerations).
3. Oracle Community Idea Lab: dynamic wait time request in OIC (illustrates the need).