PL/SQL - Autonomous Transactions (Pragma)

Card Puncher Data Processing

About

An autonomous transaction is an independent transaction that can be called from another transaction, called the main transaction.

Autonomous transactions are useful for actions that must be performed independently, regardless of whether the calling transaction commits or rolls back.

You can:

  • suspend the calling transaction,
  • perform SQL operations and commit or undo them in the autonomous transaction,
  • and then resume the calling transaction.

Example

An autonomous transaction can be useful in this case:

  • in a stock purchase transaction, you want to commit customer data regardless of whether the overall stock purchase goes through.
  • you want to log error messages to a debug table even if the overall transaction rolls back.

Characteristics

Autonomous transactions have the following characteristics:

  • The autonomous transaction does not see uncommitted changes made by the main transaction and does not share locks or resources with the main transaction.
  • Changes in an autonomous transaction are visible to other transactions upon commit of the autonomous transactions. Thus, users can access the updated information without having to wait for the main transaction to commit.
  • Autonomous transactions can start other autonomous transactions. There are no limits, other than resource limits, on how many levels of autonomous transactions can be called.

PL/SQL

An autonomous transaction executes within an autonomous scope, which is a routine marked with the pragma AUTONOMOUS_TRANSACTION.

In this context, routines include top-level anonymous PL/SQL blocks and PL/SQL subprograms and triggers.

A pragma is a directive that instructs the compiler to perform a compilation option. The pragma AUTONOMOUS_TRANSACTION instructs the database that this procedure, when executed, is to be executed as a new autonomous transaction that is independent of its parent transaction.

The figure below shows how control flows from the main routine (MT) to an autonomous routine and back again. The main routine is proc1 and the autonomous routine is proc2. The autonomous routine can commit multiple transactions (AT1 and AT2) before control returns to the main routine.

  • When you enter the executable section of an autonomous routine, the main routine suspends.
  • When you exit the autonomous routine, the main routine resumes.

In the figure above, the COMMIT inside proc1 makes permanent not only its own work but any outstanding work performed in its session. However, a COMMIT in proc2 makes permanent only the work performed in the proc2 transaction. Thus, the COMMIT statements in transactions AT1 and AT2 have no effect on the MT transaction.

Documentation / Reference





Discover More
Obiee Connexion Alter Nls Date Format
OBIEE - Connection Script

The connection pool of the physical layer gives the possibility in the Connection Script tab to execute a command script: on connect before query after query on disconnect You can call...
Card Puncher Data Processing
Oracle Database - Transactions

All Oracle transactions comply with the basic properties of a database transaction, known as ACID properties. A transaction begins when the first executable SQL statement is encountered. An executable...
Card Puncher Data Processing
PL/SQL - Pragma

In PL/SQL, a pragma is a directive that instructs the compiler to perform a compilation option. A pragma is a compiler directive that is processed at compile time, not at run time. User defined...



Share this page:
Follow us:
Task Runner