AUTONOMOUS_TRANSACTION Pragma

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction. For more information, see "Doing Independent Units of Work with Autonomous Transactions".

Syntax

Description of the illustration autonomous_transaction.gif

Keyword and Parameter Description

PRAGMA

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.

Usage Notes

You can apply this pragma to:

• Top-level (not nested) anonymous PL/SQL blocks

• Local, standalone, and packaged functions and procedures

• Methods of a SQL object type

• Database triggers

You cannot apply this pragma to an entire package or an entire an object type. Instead, you can apply the pragma to each packaged subprogram or object method.

You can code the pragma anywhere in the declarative section. For readability, code the pragma at the top of the section.

Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back.

Unlike regular triggers, autonomous triggers 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.

Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default). If you set the isolation level of the main transaction to SERIALIZABLE, changes made by its autonomous transactions are not visible to the main transaction when it resumes.

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. Oracle 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, Oracle raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.

Examples

The following example marks a packaged function as autonomous:

CREATE PACKAGE banking AS
   FUNCTION balance (acct_id INTEGER) RETURN REAL;
END banking;
/

CREATE PACKAGE BODY banking AS
   FUNCTION balance (acct_id INTEGER) RETURN REAL IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      my_bal REAL;
   BEGIN
      NULL;
   END;
END banking;
/

DROP PACKAGE banking;

The following example lets a trigger issue transaction control statements:

CREATE TABLE anniversaries AS
   SELECT DISTINCT TRUNC(hire_date) anniversary FROM employees;
ALTER TABLE anniversaries ADD PRIMARY KEY (anniversary);

CREATE TRIGGER anniversary_trigger
   BEFORE INSERT ON employees FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO anniversaries VALUES(TRUNC(:new.hire_date));
-- Only commits the preceding INSERT, not the INSERT that fired
-- the trigger.
   COMMIT;
   EXCEPTION
-- If someone else was hired on the same day, we get an exception
-- because of duplicate values. That's OK, no action needed.
      WHEN OTHERS THEN NULL;
END;
/

DROP TRIGGER anniversary_trigger;
DROP TABLE anniversaries;

Related Topics

EXCEPTION_INIT Pragma, RESTRICT_REFERENCES Pragma, SERIALLY_REUSABLE Pragma