AUTONOMOUS_TRANSACTION Pragma
The AUTONOMOUS_TRANSACTION pragma marks a routine as autonomous; that is, independent of the main transaction.
In this context, a routine is one of the following:
-
Top-level (not nested) anonymous PL/SQL block
-
Standalone, packaged, or nested subprogram
-
Method of a SQL object type
-
Database trigger
When an autonomous routine is invoked, the main transaction is suspended. The autonomous transaction is fully independent of the main transaction: they share no locks, resources, or commit dependencies. The autonomous transaction does not affect the main transaction.
Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. They become visible to the main transaction when it resumes only if its isolation level is READ COMMITTED (the default).
autonomous_transaction_pragma ::=
Description of the illustration auto_trans_pragma.gif
Keyword and Parameter Descriptions
Signifies that the statement is a pragma (compiler directive). Pragmas are processed at compile time, not at run time. They pass information to the compiler.
Signifies that the routine is autonomous.
You cannot apply this pragma to an entire package, but you can apply it to each subprogram in a package.
You cannot apply this pragma to an entire an object type, but you can apply it to each method of a SQL object type.
Unlike an ordinary trigger, an autonomous trigger can contain transaction control statements, such as COMMIT and ROLLBACK, and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.
In the main transaction, rolling back to a savepoint located before the call to the autonomous subprogram does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction.
If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. The database raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.
If you try to exit an active autonomous transaction without committing or rolling back, the database raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.
You cannot execute a PIPE ROW statement in your autonomous routine while your autonomous transaction is open. You must close the autonomous transaction before executing the PIPE ROW statement. This is normally accomplished by committing or rolling back the autonomous transaction before executing the PIPE ROW statement.