What is the pragma autonomous_transaction in PL/SQL?

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.