Search Results dbms_transaction




Overview
Explore in AI Chat

Overview

SYS.DBMS_TRANSACTION is a server-side PL/SQL package that exposes SQL transaction control statements to stored procedures, packages, and other PL/SQL program units within the Oracle E-Business Suite environment. In Oracle EBS 12.1.1 and 12.2.2, transactional integrity across the Applications schema is fundamental to order processing, general ledger posting, inventory movement, and every other business flow that writes data. Rather than embedding literal SQL transaction statements such as SET TRANSACTION READ ONLY or COMMIT COMMENT into application code, EBS developers invoke DBMS_TRANSACTION procedures, which offer equivalent functionality through a stable packaged interface.

The package declares AUTHID CURRENT_USER, meaning that execution privileges and schema resolution follow the invoking user rather than the package owner (SYS). This design is significant in the EBS context, where APPS synonyms and the applications security model govern object access. The package also supplies monitoring functions that return transaction identifiers and the ordering of transaction steps, supporting diagnostics and reconciliation of distributed or in-doubt transactions.

Key Procedures and Functions

The documented interface comprises eighteen procedures and functions. The transaction mode procedures include READ_ONLY and READ_WRITE, equivalent to the SQL SET TRANSACTION statements that establish the access mode for the current transaction. ADVISE_ROLLBACK, ADVISE_NOTHING, and ADVISE_COMMIT map to the ALTER SESSION ADVISE statements, instructing the database on how to handle a distributed transaction that cannot reach a consistent outcome; these are relevant in EBS environments using distributed or two-phase commit operations.

Commit and rollback operations are provided through COMMIT, COMMIT_COMMENT, COMMIT_FORCE, ROLLBACK, ROLLBACK_SAVEPOINT, and ROLLBACK_FORCE. COMMIT_COMMENT associates a text comment with the commit, a practice useful in EBS concurrent processing where audit trails of transaction boundaries may be required. COMMIT_FORCE and ROLLBACK_FORCE support manual resolution of in-doubt distributed transactions by transaction identifier and system change number.

Additional procedures include SAVEPOINT, which establishes a named savepoint within the transaction; USE_ROLLBACK_SEGMENT, which designates a rollback segment by name; BEGIN_DISCRETE_TRANSACTION, which initiates a discrete transaction to reduce contention and overhead; PURGE_MIXED and PURGE_LOST_DB_ENTRY, which clean up entries associated with mixed and lost in-doubt transactions; and the monitoring functions LOCAL_TRANSACTION_ID and STEP_ID, which return identifying information about the current transaction and its step sequence.

Tables Accessed

The ETRM metadata for SYS.DBMS_TRANSACTION documents no application tables referenced through APPS synonyms. This is consistent with the package's role: its procedures and functions operate on the transaction control layer of the Oracle database rather than on EBS application data. Persistent state associated with distributed transactions is managed in data dictionary views such as DBA_2PC_PENDING and DBA_2PC_NEIGHBORS, which the purge and force procedures interact with indirectly. No direct table-level dependencies are exposed to EBS development or customization.

Usage Notes

DBMS_TRANSACTION is referenced by eleven other packages in the documented EBS repository, indicating its integration into shared transaction-handling utilities rather than direct invocation from forms. In typical EBS usage, the package is called from stored procedures and custom PL/SQL that must control transaction boundaries explicitly, particularly where autonomous or distributed transactions require forced commit or rollback. Administrators may employ COMMIT_FORCE and ROLLBACK_FORCE alongside PURGE_MIXED and PURGE_LOST_DB_ENTRY when resolving in-doubt transactions reported by the database.

Because the package is owned by SYS and executes with invoker's rights, EXECUTE privileges must be granted to the invoking schema or role in custom implementations. In EBS 12.1.1 and 12.2.2 alike, standard application code generally relies on the framework's own commit and rollback discipline within forms and concurrent managers; direct calls to DBMS_TRANSACTION are reserved for specialized transaction-control or recovery scenarios.