Developing PL/SQL Procedure Calls -Based BI Publisher Reports in Oracle Fusion Cloud

In Oracle Fusion Cloud, Business Intelligence Publisher (BIP) reports can be developed using various data sources as datasets. Although Fusion Cloud is a SaaS environment, BIP reports fundamentally run on the underlying Oracle WebLogic Server (WLS) infrastructure. WebLogic is responsible for hosting, scheduling, security, thread management, and overall execution control. As a result, performance issues and timeouts often relate to WebLogic thread and JVM settings, even though these details are abstracted from end users in the cloud environment. Scheduled report jobs also use separate JVMs for improved efficiency and isolation.

Execution Time Limits for BIP Reports in Oracle Fusion Cloud

A standard scheduled BIP report (running as a standalone scheduled process, not as an ESS job) can typically run for about 40–50 minutes before reaching the maximum allowed execution time.

Interactive (online) BIP reports have stricter limits because they run in memory and must avoid blocking WebLogic threads. For this reason, online execution usually has SQL timeouts around 500–600 seconds, beyond which the report will fail to protect server resources.

Therefore, any report expected to run for a longer duration should be executed as a scheduled (offline) report or, preferably, by creating and submitting an ESS job, which provides more robust handling for long-running processes.

OIC Synchronous Call Timeout Limit for BIP Reports

For one client, we developed multiple BI Publisher (BIP) reports to support Oracle Integration Cloud (OIC) integrations that invoke these reports either on demand or through scheduled triggers, with XML as the required output format. Because OIC enforces a strict five-minute (300-second) timeout for synchronous calls, each BIP report must finish within this window; otherwise, the integration fails and subsequent processing steps do not execute.

Several reports contained multiple datasets and complex queries with extensive use of UNION ALL, often repeating the same logic across different unions or nested queries. This redundancy significantly increased report execution time, frequently causing it to exceed the five-minute limit. The timeout is a standard OIC service constraint intended to preserve platform stability and overall performance by preventing long-running requests from consuming excessive system resources.

Solution: Procedure Call based data sets (PL/SQL)

To address the performance challenges, we optimized these reports by converting the standard SQL-based datasets into PL/SQL blocks using the “Procedure Call” SQL type. A PL/SQL block, similar to a regular Oracle PL/SQL anonymous block, allows us to declare variables, perform calculations, and execute SELECT INTO statements. By selecting values once and storing them into variables for reuse within the main query logic, we eliminated redundant query execution and significantly improved performance.

BI Publisher supports executing PL/SQL anonymous blocks using callable statements, enabling the block to perform computations and return the required result set. This approach not only streamlined the query logic but also ensured faster execution times, helping the reports consistently complete within the OIC five-minute timeout window.

Requirements & Conventions

• The PL/SQL block must return a result set of type REF cursor.
• Declare the OUT variable with the reserved name xdo_cursor.
• Declare the data model parameter with the same name (xdo_cursor).
• Use xmlRowTagName=”” in the data model definition to set a valid XML row tag name for non-standard SQL datasets (defaults to ROW if empty).

Implementation Details

1) Anonymous Block Structure
The PL/SQL anonymous block must be written using the following syntax:

2) Conditional Query Execution within a Single Dataset
In a PL/SQL anonymous block, you can incorporate conditional logic to control which SQL query executes at runtime. A single data set can contain multiple SQL sections, but only the section whose condition evaluates to TRUE will run. The expression is evaluated during execution, and the corresponding SQL block is triggered based on the Boolean result.

The limitations are:

• The following syntax is supported to evaluate expressions: $if{, $elseif{, $else{
• The expression must return true or false.
• Only the following operators are supported: ==, <=, >=, <, >

3) Data Model Considerations

• Oracle PL/SQL statements start with BEGIN; metadata is not displayed in the data model structure tab.
• You cannot modify the data structure or fields via Query Builder; enter code directly in the text box.
• Define xmlRowTagName for non-standard SQL datasets to control XML row element naming.

Optimization Techniques & Best Practices

While writing PL/SQL procedure calls, consider the following best practices:

1. Minimize Repeated Table Access
   Avoid multiple subqueries that fetch different columns from the same table repeatedly. Instead, join the table once in the main query and select all required columns together.

2. Use Indexed / Primary Key Columns in Filters
   Always use indexed columns, primary keys, or highly selective predicates in filtering logic in WHERE clauses, join conditions and Procedure parameters.
3. Avoid Functions on Filtered Columns
   Applying functions to database columns inside WHERE conditions can prevent Oracle from using indexes efficiently.
4. Limit Data Early and Avoid Unnecessary Sorting
   Apply filters as early as possible before joins and unions, and avoid unnecessary ORDER BY clauses.
5. Keep XML Output Size Minimal for Faster OIC / REST API Integrations
   Large XML outputs from Oracle Fusion BIP reports can significantly slow down:

• BIP report generation
• OIC integration processing
• REST/SOAP payload transmission
• AI agent execution
• ESS job completion

To improve performance, reduce the XML payload size as much as possible.

Limitations

• Metadata is not visible; column grouping and cross-dataset linking are restricted.
• Fields from PL/SQL datasets cannot be used for bursting queries.
• Query Builder cannot modify or build PL/SQL datasets.

Performance Comparison

Conclusion

PL/SQL procedure-call datasets in BI Publisher provide an efficient, code-centric approach to meeting OIC’s strict 5-minute execution limit without requiring any infrastructure changes. By consolidating business logic, utilizing REF cursors, and applying conditional execution within a single PL/SQL block, teams can eliminate redundant SQL operations and achieve more predictable, optimized performance.

This technique is particularly useful for complex or large reports that do not rely on advanced BI Publisher features such as bursting, dataset links, or data-model–level grouping. For such scenarios, the PL/SQL approach offers a streamlined alternative that significantly improves execution time.

To ensure long-term reliability, teams should invest in proper instrumentation, governance, and comprehensive testing practices. This helps preserve performance gains and supports consistent behavior as data volumes and business requirements evolve.

About the Author

With over 14 years of experience as an Oracle ERP Technical Consultant, I bring a strong track record of delivering solutions in the information technology and services industry. I specialize in both Oracle Fusion and Oracle E-Business Suite environments, with expertise in Oracle Integration Cloud (OIC), Fusion Application Composer, bank integrations (inbound and outbound), BIP/OTBI reporting, OA Framework, SQL, PL/SQL, Oracle Forms, Reports, and Workflow. My experience spans multiple modules, including Purchasing (PO), Inventory (INV), Order Management (OM), Oracle Process Manufacturing (OPM), Accounts Payable (AP), Accounts Receivable (AR), General Ledger (GL), Warehouse Management System (WMS), and Services.