Search Results store_values

Overview

The OWA_OPT_LOCK package is a component of the Oracle Web Agent (OWA) toolkit, delivered within the OWAPUB schema and exposed to Oracle E-Business Suite environments at versions 12.1.1 and 12.2.2. Its business function is to provide optimistic locking and row-level concurrency control for web-enabled PL/SQL applications. Optimistic locking allows multiple users to read the same database row without holding a lock, and only verifies at update time that the row has not been modified by another session since it was retrieved. OWA_OPT_LOCK supports this pattern by computing checksums, storing a snapshot of a row's column values, and later validating those stored values against the current state of the row before an update is committed. This prevents lost updates in stateless HTTP sessions where database locks cannot be held across requests. In the Oracle EBS context, the package is owned by SYS and classified under the OTHER API classification, indicating it is a utility or infrastructure package rather than a business-entity API. It is referenced by no other documented packages, meaning its consumption is primarily direct, typically from Oracle PL/SQL Server Pages (PSP), mod_plsql applications, or custom web-enabled forms and procedures rather than through any framework-level abstraction.

Key Procedures and Functions

The documented API exposes four callable units, with the first requiring emphasis on parameters, since accurate signature usage is essential for correct interoperability.

• CHECKSUM — Calculates a checksum value used to detect changes in row content. Two overloads are documented. The first accepts a single buffer parameter and returns a number, suitable for hashing an arbitrary string or concatenated column buffer. The second accepts an owner, table name, and rowid, returning a number; this variant computes a checksum directly against a database row identified by its physical address. The checksum function underpins the detection logic used by verify_values.
• STORE_VALUES — A procedure that captures and stores the current column values of a specified row. It accepts the schema owner, the table name, and the rowid of the target row. The stored values are retained so that a later update can confirm the row remains unchanged. This is the routine the user searched for, and it represents the entry point for establishing an optimistic lock snapshot.
• VERIFY_VALUES — A function that accepts a previously stored values array (vcArray) and returns a boolean indicating whether the current row state still matches the stored snapshot. A TRUE result means the row is unmodified and safe to update; FALSE signals a conflict, allowing the application to raise an error or re-read the data.
• GET_ROWID — A function that accepts the stored values array and returns the rowid of the corresponding row. This allows an application to rediscover the physical address of a row after a stateless interval, using the retained snapshot rather than re-querying by primary key.

The package declares a PLPGSQL-style collection type, vcArray, defined as a table of VARCHAR2(2000) indexed by binary_integer. This array type is the carrier for the stored row snapshot passed between STORE_VALUES, VERIFY_VALUES, and GET_ROWID. The package body and its functions are marked with PRAGMA RESTRICT_REFERENCES, asserting WNDS, RNDS, WNPS, and RNPS purity. This guarantees that the functions do not write database state or package state, making them callable from SQL statements and safe within read-consistent queries, an important property for the checksum and get_rowid functions that may be invoked in SELECT contexts.

Tables Accessed

The ETRM metadata documents no tables referenced via APPS synonyms for this package. OWA_OPT_LOCK operates dynamically. The STORE_VALUES procedure and the rowid-based CHECKSUM overload construct and execute dynamic SQL against whatever table is named by the p_owner and p_tname parameters. There is no static dependency on a fixed base table. Consequently, the tables accessed are determined at runtime by the calling application. The package does not persist snapshots to a dedicated repository table; instead, stored values are returned to the caller as a vcArray collection, and the application is responsible for retaining that collection, commonly in a hidden form field, a session variable, or a package-level structure, between the read request and the subsequent update request.

Usage Notes

OWA_OPT_LOCK is typically invoked in stateless web applications built on mod_plsql or PSP within the Oracle EBS technology stack. The standard workflow proceeds as follows: on page load, the application calls STORE_VALUES with the owner, table, and rowid of the row being edited; the resulting vcArray is carried forward in the HTML form or session state; on submission, the application calls VERIFY_VALUES to confirm the row has not changed, and GET_ROWID to recover the physical address if necessary. When verification succeeds, the update proceeds; when it fails, the application raises a lost-update error and prompts the user to reapply changes. Because the package is owned by SYS and referenced by no other documented packages, custom code is the normal consumer. Developers must ensure the connected schema has appropriate privileges to select from the target table when the rowid overloads resolve row content. The RESTRICT_REFERENCES pragmas further mean these functions may be embedded in SQL for set-based checksum comparisons, for example in validation queries. In Oracle EBS 12.2.2, the package remains a lower-level web utility rather than an EBS business API, and it should be treated as infrastructure for custom web extensions rather than as part of the core EBS data model.