Blog › ICP guides

Oracle Forms developer on retainer: SELECT FOR UPDATE lock held by idle operator session, Oracle Forms 6i developer, Oracle Forms 10g developer on monthly retainer

October 10, 2026 · ~15 min read

An Oracle Forms developer was maintaining a manufacturing order management application for a discrete manufacturer running Oracle Forms 10gR2 (version 10.1.2.3) against an Oracle Database 11gR2 instance, with twenty production schedulers connecting simultaneously from Windows workstations via the Oracle Forms Java Applet client over the plant’s corporate intranet. The application — originally developed in Oracle Forms 6i in the early 2000s for Oracle 9i and migrated to Forms 10g when the manufacturer upgraded their Oracle E-Business Suite infrastructure — managed the complete manufacturing order workflow: order creation from engineering bills of material, work-in-process scheduling, component issue from inventory, operation time recording, and finished goods receipt. The principal data entry form, MFGORD.fmb, contained a master block named ORDERS backed by the ORDERS table, with child blocks ORDER_LINES, WORK_ORDERS, and OPERATIONS. The ORDERS block had its Locking Mode property set to AUTOMATIC — the Oracle Forms default — which meant that Oracle Forms would fire a SELECT ... FOR UPDATE NOWAIT statement against the ORDERS row as soon as the operator navigated into the block and pressed the first key in any navigable item. The lock was acquired at that moment by the operator’s Oracle session. On a Tuesday morning during peak scheduling, production scheduler HVAC-EST-04 at workstation PROD-OPS-WS04 queried manufacturing order MFG-2024-09847 (a production order for 500 units of product SKU PRD-2024-0003, a precision-machined valve body assembly), clicked into the ORDER_QTY field to update the quantity from 500 to 480 due to a material shortage, and Oracle Forms fired SELECT ORDER_ID, ORDER_QTY, PROD_ITEM_ID, SCHED_DATE FROM ORDERS WHERE ORDER_ID = 'MFG-2024-09847' FOR UPDATE NOWAIT. Oracle granted the row lock, associating it with session 147. The operator’s phone rang; they took the call, minimized the Oracle Forms window, and walked to the floor supervisor’s office. Two hours later, session 147 remained active (STATUS=INACTIVE, LAST_CALL_ET=7432 seconds) with the exclusive row lock still held on the ORDERS row for MFG-2024-09847. Two other production schedulers at PROD-OPS-WS07 and PROD-OPS-WS11 attempting to access the same order either received ORA-00054: resource busy and acquire with NOWAIT specified, or timeout expired immediately (because their Forms blocks also used NOWAIT locking) or sat blocked watching the screen spin while their Oracle sessions waited on the row lock.

The root cause was the interaction between Oracle Forms’ AUTOMATIC locking mode and the Oracle Database row lock lifetime model. Oracle Database row locks are session-level resources: a row lock acquired by a SELECT ... FOR UPDATE statement is held by the Oracle session that issued the statement until the session issues a COMMIT or ROLLBACK — not until the cursor is closed, not until the PL/SQL block ends, not until the Forms trigger exits, not until the operator navigates away from the block, and not until the Oracle Forms application is minimized or the window loses focus. The lock is held by the Oracle kernel at the session level, visible in V$LOCK as a TX (transaction) lock with LMODE=6 (exclusive) for the row, and visible in V$LOCKED_OBJECT with the object name (ORDERS), the row’s file number, block number, and row number. The only events that release an Oracle row lock acquired by SELECT ... FOR UPDATE are: the session issuing COMMIT or ROLLBACK (either explicitly by the operator or implicitly by DDL); the Oracle session being terminated (the ALTER SYSTEM KILL SESSION command, or the client application closing the database connection); or the database being restarted. Oracle Forms AUTOMATIC locking mode fires the SELECT ... FOR UPDATE on the first keystroke in the block, which in a busy manufacturing order management form means the lock is acquired the moment the operator starts editing — which may be tens of minutes before they intend to commit, and hours before they actually commit if they are interrupted. The lock is not released when the operator navigates to a different block, navigates to a different record in the same block, minimizes the Forms window, or leaves their workstation. It is held for the entire duration of the Oracle session’s open transaction — from the first keystroke until COMMIT, ROLLBACK, or session termination.

The single-operator development testing invisibility follows the same pattern as every multi-user lock bug that is undetectable when the developer tests with a single database session. The developer who built the MFGORD form set LOCKING_MODE to AUTOMATIC because it was the default and because in the single-user development database, the lock had no visible consequence: the developer opened the form, edited a record, confirmed the lock was acquired (by checking V$LOCK in a DBA session), and observed that committing the change released the lock correctly. No other session competed for the lock during development testing. In production with twenty concurrent production schedulers, the collision became visible during morning scheduling peak when multiple operators were accessing the same high-priority manufacturing orders simultaneously. The lock diagnostic confirmed the situation unambiguously: V$LOCK showed TYPE=TX, LMODE=6 for session 147; V$SESSION showed session 147 with username=MFGFORMS, program=FORMS60W.exe, machine=PROD-OPS-WS04, STATUS=INACTIVE, LAST_CALL_ET=7432 (the session had been idle for 2 hours and 3 minutes); V$LOCKED_OBJECT confirmed OBJECT_NAME=ORDERS and the specific row. Sessions 301 (PROD-OPS-WS07) and 312 (PROD-OPS-WS11) were visible in V$SESSION with EVENT=‘enq: TX - row lock contention’ and WAIT_TIME=0 (actively waiting), blocked by session 147.

Oracle Forms in 2026: the 4GL that powers government, healthcare, and manufacturing on Oracle Database

Oracle Forms (originally SQL*Forms, then Oracle Forms 3, 4, 4.5, 5, 6, 6i, 9i, 10g, 11g, 12c, and now Oracle Forms Services as part of Oracle Fusion Middleware) is Oracle Corporation’s proprietary 4GL application development tool for building database-connected transactional applications. Oracle Forms occupies a category that has no modern equivalent: it is a rapid application development environment in which a developer draws form canvases visually, defines data blocks that map directly to database tables, sets item properties, writes PL/SQL triggers that execute in the Oracle Database context, and deploys the application without writing a conventional application tier. The Forms application is itself the presentation, business logic, and database interface layer in a single compiled module (the FMB source file and the FMX compiled file). Oracle Forms first shipped in the mid-1980s, when “client-server database application” was synonymous with building on Oracle’s platform. Organizations that adopted Oracle Forms in the late 1980s, 1990s, and early 2000s built deeply integrated transactional systems — manufacturing MRP, government procurement, hospital clinical systems, utility billing, banking teller systems — that continue running in 2026 because the cost and risk of replacing a deeply customized Oracle Forms application with a modern web application is high, Oracle Corporation continues shipping Oracle Forms and supporting Forms 12c deployments on Oracle Fusion Middleware 12.2.1.x, and the Oracle Forms application typically performs correctly (the Forms/Oracle Database interface is mature, and the 4GL is well-suited to the transactional data entry workloads it was designed for).

The Oracle E-Business Suite (EBS) connection amplifies Oracle Forms developer scarcity. Oracle E-Business Suite — Oracle’s flagship ERP, financial, and supply chain application suite used by thousands of medium and large enterprises — uses Oracle Forms for its transactional user interface in all releases through EBS R12.2. EBS R12.2 ships hundreds of Oracle Forms modules for General Ledger, Accounts Payable, Accounts Receivable, Inventory, Purchasing, Order Management, Manufacturing, and Human Resources. Organizations running EBS R12.2 in 2026 need Oracle Forms developers for customizations (writing CUSTOM.pll triggers that hook into Oracle’s seeded forms without modifying Oracle-owned FMB modules), personalizations (using Oracle Form Personalization to add conditional field behavior without code), and troubleshooting (diagnosing performance issues, locking behavior, and Forms trigger execution order in the EBS environment). EBS R12.2 is itself a long-lived platform: Oracle committed to extended support through at least 2032, meaning organizations that chose EBS R12.2 will be running Oracle Forms-based UI for at least a decade. The pool of developers who understand Oracle Forms trigger architecture, the CUSTOM.pll extension model, EBS security groups, and the interaction between Forms blocks, database commits, and Oracle row lock behavior is contracting by attrition. An Oracle Forms developer who can diagnose LOCKING_MODE bugs, write CUSTOM.pll triggers correctly, and navigate the EBS Forms deployment infrastructure is genuinely scarce and commands rates reflecting that scarcity.

Oracle Forms locking internals: LOCKING_MODE, SELECT FOR UPDATE, V$LOCK diagnostics, and the DELAYED mode fix

Oracle Forms provides three settings for the block-level Locking Mode property that controls when Oracle Forms acquires the Oracle row lock on a record being edited. AUTOMATIC (the default): Oracle Forms fires a SELECT ... FOR UPDATE NOWAIT (in Forms 6i) or SELECT ... FOR UPDATE WAIT n (in Forms 10g/12c, where n is configurable via the lock_wait_time parameter in formsweb.cfg) against the block’s base table row when the operator performs the first keystroke in any navigable item in the block. The lock is acquired immediately upon the first edit, held for the entire duration of the operator’s open Forms transaction (which spans from the first lock acquisition until the operator commits or rolls back), and visible in V$LOCK as a TX lock from the moment of acquisition. DELAYED: Oracle Forms defers the SELECT ... FOR UPDATE until immediately before the DML statement that modifies the row (the INSERT, UPDATE, or DELETE fired at commit time). The lock is held only for the few milliseconds between the SELECT ... FOR UPDATE that validates the row has not changed since the operator queried it, the DML that writes the change, and the COMMIT that releases the lock. In concurrent environments, DELAYED mode dramatically reduces the window during which a row lock is held: instead of “held from first keystroke until commit” (potentially hours), the lock is held for “commit operation duration” (typically milliseconds). The trade-off of DELAYED over AUTOMATIC is that DELAYED does not prevent a second operator from modifying the same row during the time the first operator is editing it in-memory — if two operators both query order MFG-2024-09847 and both edit ORDER_QTY, DELAYED mode will detect the conflict at commit time (the row has changed since it was queried) and present the operator with a “Another user has changed this record” dialog, requiring the operator to re-query and re-apply their change. IMMEDIATE: Oracle Forms fires SELECT ... FOR UPDATE when the operator navigates to the record in query-results mode, before any keystroke — earlier even than AUTOMATIC. IMMEDIATE is rarely appropriate in concurrent environments and is unused in most production Forms applications.

The V$LOCK diagnostic query for identifying Oracle Forms lock holders in production: SELECT s.SID, s.SERIAL#, s.USERNAME, s.PROGRAM, s.MACHINE, s.STATUS, s.LAST_CALL_ET, l.TYPE, l.LMODE, o.OBJECT_NAME FROM V$LOCK l JOIN V$SESSION s ON l.SID = s.SID LEFT JOIN V$LOCKED_OBJECT lo ON lo.SESSION_ID = l.SID LEFT JOIN DBA_OBJECTS o ON lo.OBJECT_ID = o.OBJECT_ID WHERE l.TYPE = 'TX' AND l.LMODE = 6 ORDER BY s.LAST_CALL_ET DESC. This query shows all sessions currently holding exclusive transaction locks (which in an Oracle Forms application corresponds to open SELECT ... FOR UPDATE lock holders), sorted by idle time descending, so the longest-idle lock holder appears first. In the manufacturing incident, this query returned session 147, username MFGFORMS, program FORMS60W.exe, machine PROD-OPS-WS04, STATUS=INACTIVE, LAST_CALL_ET=7432, OBJECT_NAME=ORDERS — confirming an Oracle Forms session at workstation WS04 had been holding an exclusive lock on the ORDERS table for 2 hours and 3 minutes. To identify the specific row locked, the query joins V$LOCKED_OBJECT to get the Oracle rowid, then joins to the ORDERS table: SELECT ROWID, ORDER_ID, ORDER_QTY FROM ORDERS WHERE ROWID = (SELECT ROW_WAIT_OBJ# FROM V$SESSION WHERE SID = 147) — or more directly by querying V$SESSION.ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, and ROW_WAIT_ROW# for the blocked sessions. The fix for the manufacturing incident: the ORDERS block Locking Mode was changed from AUTOMATIC to DELAYED in Oracle Forms Builder; a WHEN-TIMER-EXPIRED trigger was added at the form level that fires every 5 minutes and calls COMMIT_FORM if there are unsaved changes or CLEAR_FORM(NO_VALIDATE) if the form status is QUERY or NORMAL; the timer is created in WHEN-NEW-FORM-INSTANCE via CREATE_TIMER('IDLE_TIMER', 300000, NO_REPEAT) and re-created after each firing. Lock-wait events per shift: 6–10 before the fix, 0 after.

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

LOCKING_MODE AUTOMATIC with no inactivity timeout is the canonical Oracle Forms invisible production lock bug for any block used by many concurrent operators who may be interrupted during editing. It appears in the work logs of Oracle Forms retainer engagements for manufacturing ERP systems, hospital clinical systems, government procurement systems, and utility billing systems that were built on Oracle Forms 6i or 10g and have been maintained through Oracle version upgrades. The work log entry that makes this diagnosable: “Oracle Forms 10gR2 (10.1.2.3); Oracle Database 11gR2 (11.2.0.4); module MFGORD.fmb; block ORDERS; LOCKING_MODE was AUTOMATIC; V$LOCK: TYPE=TX, LMODE=6, SID=147, SERIAL#=4892, USERNAME=MFGFORMS, PROGRAM=FORMS60W.exe, MACHINE=PROD-OPS-WS04, STATUS=INACTIVE, LAST_CALL_ET=7432; V$LOCKED_OBJECT: OBJECT_NAME=ORDERS, ROW_WAIT_ROW#=8847 (ORDER_ID=MFG-2024-09847); sessions 301 (WS07) and 312 (WS11) blocking on enq: TX row lock contention; lock-wait events per shift before fix: 6–10; fix: ORDERS block LOCKING_MODE changed to DELAYED; WHEN-TIMER-EXPIRED trigger added at form level (IDLE_TIMER, 300000ms, 5-minute inactivity commit/clear); all 4 blocks in MFGORD audited; hours: 3.5h.”

Oracle E-Business Suite CUSTOM.pll customization is the most common planned Oracle Forms retainer engagement for organizations running EBS R12.2 that need to add business logic to Oracle-seeded forms without modifying Oracle-owned FMB modules. A typical engagement: the manufacturing company needs to add a real-time inventory availability check when a production scheduler enters a new ORDER_QTY in the EBS Work in Process Discrete Jobs form (WSMJBSDF.fmb), displaying a warning if the entered quantity exceeds available raw material inventory. The retainer developer writes a CUSTOM.pll EVENT procedure hook that fires on WHEN-VALIDATE-ITEM for the block WIP_JOB_SCHEDULE_MATRL and item REQUIRED_QUANTITY: the trigger queries the Oracle Inventory on-hand quantity view MTL_ONHAND_QUANTITIES_DETAIL_V for the component item and organization, compares it to the required quantity, and calls FND_MESSAGE.SET_NAME + FND_MESSAGE.RAISE to display the EBS-standard warning dialog if the required quantity exceeds available inventory. Work log: “EBS R12.2.12; CUSTOM.pll EVENT hook for WSMJBSDF.fmb; block WIP_JOB_SCHEDULE_MATRL; item REQUIRED_QUANTITY; WHEN-VALIDATE-ITEM: queries MTL_ONHAND_QUANTITIES_DETAIL_V for INVENTORY_ITEM_ID=:WIP_JOB_SCHEDULE_MATRL.COMPONENT_ITEM_ID, ORGANIZATION_ID=:WIP_JOB_SCHEDULE_MATRL.ORGANIZATION_ID; warns if SUM(TRANSACTION_QUANTITY) < :WIP_JOB_SCHEDULE_MATRL.REQUIRED_QUANTITY; FND_MESSAGE.SET_NAME('XXCUST','XXCUST_INSUFFICIENT_INV') + FND_MESSAGE.RAISE; tested against WIP and INV test instance; regression-tested all WSMJBSDF.fmb paths affected by CUSTOM.pll EVENT; no LOCKING_MODE changes (WSMJBSDF uses DELAYED already); hours: 5h.”

Oracle Forms 6i-to-12c migration is the highest-effort Oracle Forms retainer engagement for organizations that need to move from Forms 6i client-server deployment (Oracle Net8 or SQL*Net connection from Forms 6i runtime on each workstation) to Oracle Forms 12c web deployment (Forms Services on WebLogic, browser-based Java Web Start or JNLP client). The migration work involves: identifying all deprecated Forms 6i built-ins that were removed or changed in Forms 10g/12c (USER_EXIT, FORMS_DDL semantics changes, OLE2 container changes); rewriting PL/SQL library packages that rely on client-side file system operations (TEXT_IO, WEB.SHOW_DOCUMENT calls that open local files) to server-side UTL_FILE or Oracle Application Express-based file operations; updating Forms trigger code that used Forms 6i-specific properties or built-ins; testing all trigger execution order behavior (which changed subtly between Forms 6i and Forms 10g); and configuring the WebLogic domain, Oracle HTTP Server, and formsweb.cfg parameters (including lock_wait_time, session_timeout, and idle_timeout) for the production environment. Work log entry: “MFGORD.fmb Forms 6i to 12c migration; Oracle Forms 12.2.1.11; WebLogic 12.2.1.4; 3 deprecated USER_EXIT calls replaced with EXEC_SQL or server-side PL/SQL; TEXT_IO replaced with UTL_FILE server-side in PRINT_WORK_ORDER procedure; WHEN-NEW-FORM-INSTANCE modified to remove client-side registry read (no equivalent in 12c web deployment — replaced with profile option lookup via FND_PROFILE.VALUE); all 47 triggers regression-tested against manufacturing test database; lock_wait_time set to 10 seconds in formsweb.cfg; session_timeout set to 1800 seconds; idle_timeout set to 300 seconds (aligns with inactivity timer fix deployed in prior engagement); hours: 28h migration + 12h testing.”

How HourTab tracks Oracle Forms developer retainer hours

Oracle Forms LOCKING_MODE AUTOMATIC lock hold bugs are invisible in single-user development testing by the same mechanism that makes all multi-user lock bugs invisible when only one session accesses the data at a time. The developer who builds MFGORD.fmb tests the ORDERS block in a development Oracle Forms instance against a development Oracle Database: they query a test production order, click into ORDER_QTY, observe that Oracle Forms acquires the row lock (visible in V$LOCK on the development DB in a DBA SQL*Plus session), enter a new quantity, commit, and verify the lock is released. The row lock is held from the first keystroke to the commit — but with only one Oracle session accessing the ORDERS row, no other session competes for the lock, no lock wait event fires, and the developer never observes the multi-user contention that will occur in production with twenty concurrent production schedulers accessing the same high-priority manufacturing orders during morning scheduling peak. The invisible lock acquisition is revealed only in production, when a second operator’s session blocks on enq: TX - row lock contention and the DBA queries V$SESSION to find the culprit: an INACTIVE Forms session at a workstation where the operator left to take a phone call 2 hours ago.

The work log entry that makes Oracle Forms lock work auditable must name every element of the causal chain: the Oracle Forms version and Oracle Database version (Forms 10gR2 on Oracle 11gR2 vs. Forms 12c on Oracle 19c — connection semantics and V$LOCK view structure differ), the module name and block name, the old and new LOCKING_MODE setting, the V$LOCK diagnostic output (SID, SERIAL#, USERNAME, PROGRAM, MACHINE, STATUS, LAST_CALL_ET, OBJECT_NAME, lock type and mode), the blocked sessions and their wait events, the lock-wait frequency before and after the fix, and the inactivity timer configuration (interval, action, trigger name and location). A log that says “fixed Oracle Forms lock issue, 3.5h” is not auditable to a manufacturing VP reviewing a monthly retainer invoice. A log that names the module, the block, the V$LOCK evidence confirming a 2-hour INACTIVE session at a specific workstation, the LOCKING_MODE change from AUTOMATIC to DELAYED, the inactivity timer trigger added at the form level, and the before/after lock-wait frequency is auditable and demonstrates the value of a long-term Oracle Forms retainer relationship for a manufacturer whose production scheduling workflow depends on the Forms application. HourTab gives Oracle Forms developers a public retainer-hours URL they send to clients — manufacturers, government agencies, hospitals, and utilities that run core transactional operations on Oracle Forms applications maintained by an Oracle Forms specialist on monthly retainer.

The broader context for Oracle Forms retainer billing is that LOCKING_MODE AUTOMATIC without an inactivity timeout is the Oracle Forms instantiation of the universal principle that explicit lock acquisition demands a bounded lock lifetime. Progress OpenEdge ABL developer retainers cover the equivalent in Progress 4GL: FIND FIRST EXCLUSIVE-LOCK acquiring a record lock outside a transaction block, held until explicit RELEASE or session end, with the lock persisting through validation failure paths that execute RETURN without RELEASE. Gupta SQLWindows / Centura developer retainers cover the equivalent in SQLWindows: an explicit SqlPrepareAndExecute with a FOR UPDATE cursor that is not closed on the failure branch, holding the Oracle or SQL Server row lock for the SQLWindows session lifetime. Uniface developer retainers cover the equivalent in Uniface PML: a RETRIEVE with row-level locking that is not followed by ROLLBACK on the validation failure path, holding the underlying Oracle or SQL Server transaction open with row locks while concurrent Uniface operators receive lock timeout errors.

FAQ: Oracle Forms developer retainers