Autonomous Transactions - PL/SQL

Autonomous transactions are independent transactions that can be called from within another transaction.

Autonomous transactions are useful for implementing actions that need to be performed independently, regardless of whether the calling transaction commits or rolls back. Example: Logging

An autonomous transaction lets you leave the context of the calling transaction, perform some SQL operations, commit or undo those operations, and then return to the calling transaction's context and continue with that transaction.

Check the below diagram to understand this point.

 

When to Use?

• Logging Mechanism
• Commits/Rollback in DB Triggers
• Software Usage Meter

Developer Notes:

• One autonomous transaction can call another. How many levels? Depends on init.ora parameter
• Deadlocks are possible between an autonomous transaction and its calling transaction. The application developer is responsible for avoiding deadlock situations.

Restrictions

• Usage of resource from Main Transaction
• Must have Rollback or Commit
• Total Sessions restricted by init.ora parameter

Example:
Error logging routine using Autonomous Transactions concept

1. Create a table to holds basic error messages.

CREATE TABLE error_logs (

  log_timestamp  TIMESTAMP      NOT NULL,

  error_message  VARCHAR2(4000),

  CONSTRAINT error_logs_pk PRIMARY KEY (log_timestamp)

);

2. Create an autonomous Procedure to log error messages as an autonomous transaction

CREATE OR REPLACE PROCEDURE log_errors (p_error_message  IN  VARCHAR2) AS

  PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

  INSERT INTO error_logs (log_timestamp, error_message)

  VALUES (SYSTIMESTAMP, p_error_message);

  COMMIT;

END;

/

3. Now create another table

CREATE TABLE at_table (

  id           NUMBER       NOT NULL,

  description  VARCHAR2(50) NOT NULL

);

4. Here comes the main routine which calls AT procedure. The following code forces an error, which is trapped and logged.

BEGIN

  INSERT INTO at_table (id, description)

  VALUES (1, 'Description for 1');

  -- Force invalid insert. (description can not be NULL)

  INSERT INTO at_table (id, description)

  VALUES (2, NULL);

EXCEPTION

  WHEN OTHERS THEN

    /*Calling autonomous routine*/

    log_errors (p_error_message => SQLERRM);

    ROLLBACK;

END;

/

5. Here comes the output

=> select * from error_logs;

LOG_TIMESTAMP                         ERROR_MESSAGE
----------------------                          ----------------------
10/24/2011 6:44:32.803666 AM   ORA-01400: cannot insert NULL into ("APPS"."AT_TABLE"."DESCRIPTION")

Explanation: Error trapped through autonomous procedure. Data inserted into the table error_logs and committed. Remember Calling program is rolled back!

Here is the proof
=> select * from at_table
no rows returned.