Blog › ICP guides

Oracle Reports developer on retainer: SRW.DO_SQL UPDATE not committed before report execution, Oracle Reports 10g developer, Oracle Reports Services developer on monthly retainer

October 10, 2026 · ~15 min read

An Oracle Reports developer was maintaining the accounts receivable aging report AR_AGING_SUMMARY.rdf for a wholesale food distribution company running Oracle E-Business Suite R12.1.3 on Oracle Database 12.1.0.2 (Oracle 12cR1). Oracle Reports Services 10.1.2.3 was deployed on Oracle Application Server 10gR3, with the concurrent manager invoking Oracle Reports via the EBS concurrent program AR_AGING_SUMMARY_RPT. Eight accounting staff members regularly ran the report simultaneously during month-end close (the last business day of each month): two staff running the full company-wide detailed version covering 4,200 customer accounts (35-page PDF, data model queries spanning AR.RA_CUSTOMERS, AR.AR_PAYMENT_SCHEDULES_ALL, AR.AR_RECEIVABLE_APPLICATIONS_ALL, and the aging bucket computation view AR_AGING_7_BUCKETS_V), and six staff running division-specific summaries for their respective territories (300–800 customers, 8–12 pages each). A full 35-page run took 6–8 minutes; a division summary took 2–3 minutes.

The AR_AGING_SUMMARY.rdf report included a Before Report trigger — Oracle Reports’s PL/SQL hook that fires once before the data model queries execute. The trigger’s original developer had added a status flag update to a staging table AR_RPT_STAGING to track which reports were currently running (used by the Finance Director’s monitoring dashboard to display active report jobs). The trigger code read: SRW.DO_SQL('UPDATE AR_RPT_STAGING SET STATUS = ''RUNNING'', STARTED_BY = ''' || :p_user_id || ''', STARTED_AT = SYSDATE WHERE REPORT_TYPE = ''AR_AGING'' AND PERIOD_DATE = TO_DATE(''' || TO_CHAR(:p_period_date, 'DD-MON-YYYY') || ''', ''DD-MON-YYYY'') AND STATUS = ''QUEUED'''); — no SRW.DO_SQL('COMMIT') followed this statement. The first accounting staff member’s report run (EBS concurrent request ID 4589048, SID 287 in Oracle Database) executed the Before Report trigger at 08:03:14 AM on September 30, 2026. The SRW.DO_SQL UPDATE changed the AR_RPT_STAGING row for REPORT_TYPE = 'AR_AGING', PERIOD_DATE = 30-SEP-2026 from STATUS QUEUED to STATUS RUNNING, and acquired an Oracle TX exclusive lock (LMODE=6) on that row. Because no COMMIT was issued, the TX lock remained held by SID 287 for the entire duration of the report’s data model query execution — 7 minutes and 22 seconds.

At 08:03:47 AM (33 seconds later), a second accounting staff member submitted the same AR_AGING_SUMMARY_RPT concurrent program (EBS request ID 4589071, SID 341). Oracle Reports Services allocated a second dedicated Oracle session for this request. When SID 341’s Before Report trigger executed the same SRW.DO_SQL UPDATE on the AR_RPT_STAGING row for REPORT_TYPE = 'AR_AGING', PERIOD_DATE = 30-SEP-2026, the statement blocked. Oracle’s row-level locking: the AR_RPT_STAGING row had been locked in exclusive mode by SID 287’s open transaction. SID 341’s UPDATE request could not acquire the exclusive lock while SID 287 held it. Oracle Reports Services’s SRW.DO_SQL function does not specify NOWAIT — it issues a blocking UPDATE that waits indefinitely for the lock. SID 341 waited at the Before Report trigger, before its data model queries had even started. Over the next 2 minutes, six more concurrent report submissions arrived (SIDs 354, 367, 389, 401, 419, 433), each blocking at the same SRW.DO_SQL UPDATE in its Before Report trigger, queued behind SID 287’s held TX lock. By 08:05 AM, seven Oracle Reports Services sessions were blocked, waiting for one 7-minute report to complete and commit or roll back.

The Oracle Database V$LOCK diagnostic confirmed the pattern. The Oracle DBA, called in at 08:42 AM when the Finance Director reported that the month-end close report queue had been running for 39 minutes with no output, ran: SELECT h.sid AS holding_sid, h.username, h.machine, h.program, h.status, h.last_call_et AS holding_secs, w.sid AS waiting_sid, lh.lmode, lw.request FROM v$lock lh JOIN v$session h ON lh.sid = h.sid JOIN v$lock lw ON lw.id1 = lh.id1 AND lw.id2 = lh.id2 AND lw.request > 0 JOIN v$session w ON lw.sid = w.sid WHERE lh.lmode > 0 ORDER BY h.last_call_et DESC; — the result showed SID 287 (USERNAME APPS, MACHINE OASSERVER01, PROGRAM rwsrv_pld) with TX LMODE=6 LAST_CALL_ET=2304 (38 minutes 24 seconds) blocking SIDs 341, 354, 367, 389, 401, 419, and 433 with REQUEST=6. SID 287 was the first report run, now in its 38th minute of execution (the full company-wide 35-page report was taking longer than usual due to AR aging bucket recalculation for 4,200 accounts as of September 30 quarter-end). The Oracle TX lock from the Before Report trigger SRW.DO_SQL UPDATE had been held continuously since 08:03:14 AM. Seven concurrent report requests were queued, fully serialized.

The consequence: eight concurrent month-end close report requests had an expected parallel execution time of approximately 7–8 minutes (all eight running concurrently on Oracle Reports Services’ connection pool). The actual execution time, with full serialization behind SID 287’s TX lock, was 8 × 7 minutes = 56 minutes. The Finance Director’s target was for all eight reports to be available in the EBS concurrent request output viewer by 08:15 AM. The actual completion time was 09:01 AM (SID 287 completed at 08:10, released the TX lock, SID 341 immediately acquired the lock and completed at 08:17, and so on through all eight requests in sequence). The downstream consolidation process that consumed the eight aging reports to produce the quarterly DSO (Days Sales Outstanding) summary was delayed by 46 minutes, pushing the quarterly close package past the board presentation preparation deadline.

Oracle Reports and Oracle Reports Services in 2026: the EBS reporting layer still running in Oracle R11i and R12

Oracle Reports (Oracle Reports 6i, Oracle Reports 10g, Oracle Reports 10gR2) is Oracle Corporation’s report development tool for Oracle Database environments, particularly Oracle E-Business Suite. Oracle introduced Oracle Reports in the late 1980s as the reporting companion to SQL*Forms; it shipped with Oracle Developer Suite 6i (1999) and Oracle Developer Suite 10g (2004). Oracle deprecated Oracle Reports in Oracle Database 11g (2007), replacing it with Oracle BI Publisher (formerly XML Publisher, rebranded Business Intelligence Publisher). However, Oracle E-Business Suite R11i and R12 shipped with Oracle Reports as the primary output engine for hundreds of standard reports: invoices, purchase orders, payment remittances, trial balances, aging reports, inventory valuation reports, and government-mandated formatted output (1099, W-2, VAT returns, Intrastat filings). Oracle EBS R12.2 organizations that have not yet migrated to Oracle Analytics Publisher (the current name for Oracle BI Publisher in the cloud era) are still running Oracle Reports 10gR2 in production in 2026.

Oracle Reports Services is the server-side execution engine for Oracle Reports in Oracle Application Server (OAS) 10gR2 and 10gR3 environments. Oracle Reports Services consists of one or more rwserver processes, each maintaining a pool of Oracle Database connections and a cache of compiled report bytecode. When Oracle EBS submits a concurrent program request for an Oracle Reports RDF file, the concurrent manager calls the Oracle Reports Services engine via the rwrun servlet or rwclient command, which dispatches the request to an available rwserver engine. The rwserver allocates a dedicated Oracle session from its connection pool for the report job, executes the Before Report trigger on that session, runs the data model queries, formats the output, executes the After Report trigger, and writes the output file to the EBS request output directory. Oracle Reports Services supports concurrent execution of multiple report jobs across its connection pool — the intended design is that multiple EBS concurrent requests run in parallel, limited by the pool size configured in the Oracle Reports Services configuration (rwservlet.properties or the Oracle Application Server cluster configuration).

Oracle Reports developer scarcity in 2026 is acute: Oracle deprecated Oracle Reports in 2007, and no developer who started their career after 2010 would have chosen to develop Oracle Reports expertise. The developers who built the Oracle Reports RDF inventory for Oracle EBS R11i and R12 implementations in the 2000–2010 period have largely retired or shifted to other Oracle technologies. Organizations that still run Oracle Reports in 2026 face a specific challenge: their Oracle Reports RDF files contain 10–20-year-old PL/SQL SRW trigger code that was written before Oracle Reports Services was widely deployed, when most Oracle Reports were run sequentially from a single rwrun invocation rather than concurrently from an EBS concurrent manager queue. A Before Report trigger that issues SRW.DO_SQL UPDATE on a shared staging table row without a subsequent COMMIT is harmless when reports run one at a time; it serializes the entire month-end close when eight accounting staff submit the same report simultaneously.

Oracle Reports SRW.DO_SQL transaction management: COMMIT after DML, Before Report trigger TX lock release, and V$LOCK diagnostics

Oracle Reports’s SRW.DO_SQL built-in function executes a SQL statement against the Oracle session that Oracle Reports Services has allocated for the current report job. When SRW.DO_SQL executes a DML statement (INSERT, UPDATE, DELETE), Oracle acquires the appropriate row-level locks on the affected rows and places a TX entry in V$LOCK with LMODE=6 (exclusive) on the Oracle session executing the report. The TX lock is held until the session issues a COMMIT or ROLLBACK. If the Before Report trigger issues an SRW.DO_SQL UPDATE and no subsequent SRW.DO_SQL('COMMIT') is issued in the trigger, the TX lock from the UPDATE is held for the entire duration of the report: through all data model query execution (which may take 6–8 minutes for a 4,200-customer AR aging report at quarter-end), through all format trigger execution, and through the After Report trigger execution. The Oracle session is ACTIVE during data model query fetches and INACTIVE between fetches, but the TX lock is held regardless of the session’s activity status — Oracle TX locks are not automatically released by session inactivity, only by COMMIT or ROLLBACK.

The SRW.DO_SQL('COMMIT') call in the Before Report trigger, placed immediately after the staging UPDATE, releases the TX lock within milliseconds of the UPDATE completing. The COMMIT semantics of SRW.DO_SQL('COMMIT') are identical to a regular Oracle COMMIT: all pending DML changes are made durable and visible to other sessions, and all TX-associated row locks are released. After the COMMIT, the Oracle Reports Services session has no open transaction, holds no row locks, and can proceed with the data model queries without any lock contention effect on other concurrent report sessions. The status flag in AR_RPT_STAGING is committed (STATUS = RUNNING, visible to the Finance Director’s monitoring dashboard), and the session is free to acquire new locks during the data model queries without holding the staging table row lock throughout.

One nuance: the SRW.DO_SQL('COMMIT') in a Before Report trigger commits all pending DML changes on the current Oracle session at the time of the call, not just the changes from the SRW.DO_SQL UPDATE. If the Before Report trigger had issued multiple SRW.DO_SQL DML statements before the COMMIT, all of those changes would be committed together. This is the correct behavior for a status flag update: the staging table update is an administrative flag, not part of the report’s core business transaction. Committing it immediately is semantically correct — the flag should be visible to the monitoring dashboard as soon as the report starts, regardless of whether the report completes successfully, fails, or is cancelled. After the COMMIT, if the After Report trigger needs to update the staging row back to STATUS = DONE or STATUS = FAILED, it issues another SRW.DO_SQL UPDATE followed by another SRW.DO_SQL('COMMIT').

The fix for AR_AGING_SUMMARY.rdf: the Before Report trigger was changed from:
SRW.DO_SQL('UPDATE AR_RPT_STAGING SET STATUS = ''RUNNING'', ... WHERE REPORT_TYPE = ''AR_AGING'' AND PERIOD_DATE = ... AND STATUS = ''QUEUED''');
to:
SRW.DO_SQL('UPDATE AR_RPT_STAGING SET STATUS = ''RUNNING'', ... WHERE REPORT_TYPE = ''AR_AGING'' AND PERIOD_DATE = ... AND STATUS = ''QUEUED'''); SRW.DO_SQL('COMMIT');
The After Report trigger, which updated the row to STATUS = DONE after successful completion, also received SRW.DO_SQL('COMMIT') after its UPDATE. The full Oracle Reports RDF inventory for the EBS R12.1.3 implementation was audited: 47 custom Oracle Reports RDF files were reviewed for Before Report and After Report trigger SRW.DO_SQL DML statements without a subsequent COMMIT. Four additional RDF files had the same pattern (all four involved staging table status updates); all were fixed. Concurrent report execution time for the month-end close: 56 minutes (fully serialized) to 8 minutes (parallel) after the fix.

Typical Oracle Reports developer retainer work and what it looks like in a work log

SRW.DO_SQL DML without COMMIT in Before Report trigger serializing concurrent month-end report runs is the canonical Oracle Reports invisible production lock bug for any Oracle Reports deployment where multiple users concurrently submit the same report type against a shared staging or status table. It appears in the work logs of Oracle Reports retainer engagements for Oracle EBS R11i and R12 wholesale distribution companies (AR aging and customer statement reports run at month-end by multiple A/R staff), manufacturing companies (inventory valuation and open purchase order reports run by multiple procurement staff at fiscal quarter-end), government agencies (grant expenditure and budget variance reports run by multiple program managers at fiscal year-end), and healthcare organizations (remittance advice and claims payment reports run by multiple finance staff during monthly close). The work log entry: “Oracle Reports Services 10.1.2.3; Oracle Database 12.1.0.2; EBS R12.1.3 concurrent program AR_AGING_SUMMARY_RPT; RDF file AR_AGING_SUMMARY.rdf; Before Report trigger SRW.DO_SQL UPDATE AR_RPT_STAGING STATUS QUEUED→RUNNING without SRW.DO_SQL('COMMIT'); TX LMODE=6 exclusive lock on AR_RPT_STAGING row REPORT_TYPE=AR_AGING PERIOD_DATE=30-SEP-2026 held by SID 287 for 7 minutes 22 seconds (full report execution duration); 7 concurrent sessions (SIDs 341, 354, 367, 389, 401, 419, 433) blocked on REQUEST=6 waiting for SID 287's TX lock; V$LOCK diagnosis: LAST_CALL_ET=2304 on holding session; 8 concurrent month-end runs fully serialized: 56 minutes actual vs. 8 minutes expected; fix: SRW.DO_SQL('COMMIT') added after staging UPDATE in Before Report trigger; SRW.DO_SQL('COMMIT') added after completion UPDATE in After Report trigger; full 47-RDF inventory audited — 4 additional RDFs fixed; concurrent report execution time: 56 min → 8 min; hours: 5h.”

Oracle Reports data model query performance optimization is the most common planned Oracle Reports retainer work when the report is functionally correct but takes 6+ minutes and needs to be under 2 minutes to meet the concurrent manager request SLA. The Oracle Reports data model connects to Oracle Database via the Oracle Reports Services pooled connection; each data model query is executed as a standard Oracle SELECT statement. EXPLAIN PLAN on the report’s data model queries (accessible via the Oracle Reports Builder query definition, converted to SQL with the appropriate bind parameter values for a representative reporting period) identifies missing indexes, full table scans on large AR tables, or suboptimal join order. For an AR aging report querying AR_PAYMENT_SCHEDULES_ALL (20 million rows) with a join to RA_CUSTOMERS (4,200 customers in a company-specific partition), a missing composite index on AR_PAYMENT_SCHEDULES_ALL (org_id, customer_id, due_date) can cause an 8-minute report to complete in 45 seconds. Work log: “AR_AGING_SUMMARY.rdf data model query optimization; Oracle 12cR1; AR_PAYMENT_SCHEDULES_ALL FULL TABLE SCAN identified via EXPLAIN PLAN (20.3M rows, no selective index on (org_id, customer_id, due_date)); new index AR_PS_IDX_ORG_CUST_DUE created on AR_PAYMENT_SCHEDULES_ALL (org_id, customer_id, due_date); statistics gathered; report execution time: 7 min 22 sec → 41 sec; concurrent month-end run queue time: 56 min → 6 min (parallel); hours: 4h.”

Oracle Reports to Oracle Analytics Publisher migration is the most common long-term Oracle Reports retainer engagement for EBS R12.2 organizations on Oracle 19c who are planning to move off Oracle Reports before Oracle’s Oracle Application Server 10gR3 support fully sunsets. The migration path: convert Oracle Reports RDF data model queries to Oracle Analytics Publisher RTF template data definitions (XDO data definition XML), convert Oracle Reports layout models to Oracle Analytics Publisher RTF templates or XSL-FO layouts, register the new BI Publisher concurrent programs in EBS, and decommission the Oracle Reports Services rwserver processes. A retainer engagement that migrates one Oracle Reports RDF per month over 12 months converts the core standard report inventory to Oracle Analytics Publisher while maintaining the Oracle Reports deployment for the remainder. Work log: “AR_AGING_SUMMARY.rdf migration to Oracle Analytics Publisher 12.2; data model: Oracle Reports groups and formulas converted to Oracle Analytics Publisher data definition XML (AR_AGING_RPT_DS.xdm); layout: Oracle Reports layout model converted to RTF template (AR_AGING_SUMMARY.rtf) with Microsoft Word BI Publisher Desktop add-in; EBS concurrent program AR_AGING_SUMMARY_RPT updated to execute type XML Publisher; XDO template registered in Oracle XML Publisher Administrator; 4 division-specific layout templates created (NORTH, SOUTH, EAST, WEST); output PDF verified match against Oracle Reports output for 3 test accounting periods; Oracle Reports AR_AGING_SUMMARY.rdf decommissioned; rwserver connection pool reduced by 4; hours: 16h per RDF migration.”

How HourTab tracks Oracle Reports developer retainer hours

Oracle Reports Before Report trigger SRW.DO_SQL TX lock bugs are invisible in sequential report testing by the same mechanism that makes all database concurrency bugs invisible when only one report job runs at a time. The developer who added the staging table UPDATE to the AR_AGING_SUMMARY.rdf Before Report trigger tested it by running the report once from Oracle Reports Builder or via a single EBS concurrent request. The report ran correctly: the staging row changed from QUEUED to RUNNING, the data model queries executed, the 35-page PDF was produced, and the After Report trigger changed the row from RUNNING to DONE. The lock behavior only becomes visible when eight accounting staff submit the same report concurrently during month-end close — a scenario the developer never replicated in their test environment, because replicating it requires eight concurrent Oracle sessions all accessing the same staging row within a 2-minute window.

The work log entry that makes Oracle Reports SRW.DO_SQL lock work auditable must name every element of the causal chain: the Oracle Reports Services version (10.1.2.3), the Oracle Database version (12.1.0.2), the EBS concurrent program name (AR_AGING_SUMMARY_RPT), the RDF file name (AR_AGING_SUMMARY.rdf), the specific Before Report trigger code (the SRW.DO_SQL UPDATE statement), the staging table and row identifier (AR_RPT_STAGING, REPORT_TYPE='AR_AGING', PERIOD_DATE='30-SEP-2026'), the V$LOCK diagnostic (SID 287 TX LMODE=6 LAST_CALL_ET=2304 blocking SIDs 341, 354, 367, 389, 401, 419, 433), the impact (56-minute serialized execution vs. 8-minute parallel expected), and the concurrent execution time before and after the fix. A log that says “fixed Oracle Reports concurrent execution issue, 5h” is not auditable to a wholesale food distribution company’s Finance Director who is trying to explain to the CFO why the quarterly DSO summary was 46 minutes late for the board presentation. HourTab gives Oracle Reports developers a public retainer-hours URL they send to clients — EBS R11i and R12 wholesale distribution companies, manufacturers, government agencies, and healthcare organizations that run month-end and quarter-end Oracle Reports concurrent programs maintained by an Oracle Reports specialist on monthly retainer.

The broader context for Oracle Reports retainer billing is that the SRW.DO_SQL UPDATE without COMMIT serializing concurrent report execution is the Oracle Reports instantiation of the universal principle that any system allowing long-lived exclusive locks demands explicit lock release as soon as the locking operation’s purpose is fulfilled. Oracle PL/SQL developer retainers cover the equivalent in Oracle stored procedures: SELECT ... FOR UPDATE NOWAIT in VALIDATE_AND_RESERVE_CLAIM acquires a TX LMODE=6 lock on a CLAIMS row that is held for the lifetime of the Oracle session when the procedure returns early on validation failure without ROLLBACK — same Oracle TX lock mechanism, different application layer. Oracle Forms developer retainers cover the equivalent in Oracle Forms: LOCKING_MODE AUTOMATIC fires SELECT ... FOR UPDATE NOWAIT on the Oracle ORDERS row when the production scheduler presses the first key in a field, holding the TX lock for 2+ hours when the operator walks away — same Oracle TX lock, interactive user as the lock holder. Microsoft Access developer retainers cover the equivalent on a different database engine: the ACE engine’s Edited Record pessimistic lock holds a record-level lock for the Access session lifetime when the estimator minimizes the form without saving — different platform, same class of lock held indefinitely without explicit release.

FAQ: Oracle Reports developer retainers