Defining Autonomous Transactions
To define an autonomous transaction, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler to mark the routine as the autonomous (independent). In this context, the word routine involves:
(i) The top-level anonymous PL/SQL blocks
(ii)The local, packaged functions, and stand-alone & procedures
(iii) The methods of the SQL object type
(iv)A database triggers
You can code the pragma wherever in the declarative part of a routine. But, for the readability, code the pragma at the top of the part. The syntax is as shown:
PRAGMA AUTONOMOUS_TRANSACTION;
In the illustration below, you mark 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
...
END;
END banking;