Autonomous Transactions

When an autonomous transactions is called it leaves context of calling program, perform an independent task without affecting calling program state. It is necessary to have COMMIT or ROLLBACK statement as last statement within autonomous transaction program unit executable section to avoid the run time oracle error 'ORA-06519: active autonomous transaction detected'.

It can be used to monitor the progress of a program unit and record the event/error message generated during program execution.

Following type of PL/SQL block can use PRAGMA autonomous transactions
      ·         Stored procedure and function
      ·         Procedure or function defined in declaration section of anonymous block
      ·         Packaged procedure and Function
      ·         Type Methods

An example to understand autonomous transactions

CREATE OR REPLACE PROCEDURE RecordError (errmsg IN VARCHAR2)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO errorlog (id, errmsg, lstime)
        VALUES (seq_errorlog.NEXTVAL, errmsg, SYSDATE);

   COMMIT;
END RecordError;
/

BEGIN
   FOR X IN 1 .. 100
   LOOP
      RecordError  ('Autonomous transactions message - ' || X);

      INSERT INTO errorlog (id, errmsg, lstime)
           VALUES (
                     seq_errorlog.NEXTVAL,
                     'Calling Program Message - ' || X,
                     SYSDATE);
   END LOOP;
   ROLLBACK;
END;
/

 

Execution of anonymous block will create the records in ERRORLOG table generated through RECORDERROR procedure only.