What is the pragma autonomous_transaction in PL/SQL?

by JAMUNA
(HYDERABAD)

From Jamuna, Hyderbad - What is the AUTONOMOUS_TRANSACTION pragma in Oracle PL/SQL?

PRAGMA's are directives for the Oracle PL/SQL compiler. The AUTONOMOUS_TRANSACTION pragma instructs the compiler to treat the follwoing pl/sql block as autonomous (independent) from whatever transaction calls it. This means that any changes made to the database in the autonomous transaction are independent of the main transaction and are either committed or rolled back without affecting the main transaction.

Oracle pl/sql autonomous transactions must explicitly either roll back or commit any changes before exiting and can be:-

stand alone procedures or functions
procedures/functions defined in a package (but not nested)
triggers
schema-level anonymous pl/sql blocks

Let's look at a brief example.

First we declare an anonymous transaction

CREATE OR REPLACE PROCEDURE log_details
(msg IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION
BEGIN

    INSERT INTO log(msg_id         ,log_msg,time_of_msg)
    VALUES         (log_seq.NEXTVAL,msg    ,SYSDATE);

    COMMIT; -- must commit or rollback

END;

Next, we have another tansaction that calls this procedure.

BEGIN

    DELETE employees;
    log_msg('Deleting all employees');
    ROLLBACK;
    log_msg('after rollback of delete employees');

END;

After this, the employees table would be unchanged, but there would be 2 (new) rows in the LOG table.

For the details of all the conditions for autonomous transactions see the Oracle PL/SQL Language Reference manual.

We also recommend Steven Feuerstein's and Bill Pribyl's excellent book Oracle PL/SQL Programming: Covers Versions Through Oracle Database 11g Release 2 (Animal Guide) which gets 4 stars on Amazon as the average review.