Question:
What is the idea of an automous transaction in
PL/SQL? I understand that there is the pragma automous transaction
but I am unclear about when to use automous transactions.
Answer: Autonomous transactions
are used when you wan to roll-back some code while continuing to process
an error logging procedure.
The term "automous transaction" refers to the ability of
PL/SQL temporarily suspend the current transaction and begin another,
fully independent
transaction (which will not be rolled-back if the outer code aborts). The second transaction is known as an autonomous
transaction. The autonomous transaction functions independently from the
parent code.
An autonomous transaction has the following
characteristics:
There are many times when you might want to use
an autonomous transaction to commit or roll back some changes to a table
independently of a primary transaction's final outcome.
We use a compiler directive in PL/SQL (called a
pragma) to tell Oracle that our transaction is autonomous. An autonomous
transaction executes within an autonomous scope. The PL/SQL compiler is
instructed to mark a routine as autonomous (i.e. independent) by the
AUTONMOUS_TRANSACTIONS pragma from the calling code.
As an example of autonomous transactions, let's
assume that you need to log errors into a Oracle database log table. You
need to roll back the core transaction because of the resulting error,
but you don't want the error log code to rollback. Here is an
example of a PL/SQL error log table used as an automous transaction:
As we can see, the error is logged in the
autonomous transaction, but the main transaction is rolled back.
For more details on automous transactions, see
the "Easy
Oracle Series"
and include the URL for the page.
