What is the purpose of the PRAGMA AUTONOMOUS_TRANSACTION directive?

The PRAGMA AUTONOMOUS_TRANSACTION directive is used in Oracle PL/SQL to create an autonomous transaction within the main transaction. This means that the code marked with this directive executes in a separate transaction, independent of the main one.

This is useful when you need to perform operations that should not depend on the success or failure of the main transaction. For example, logging or auditing, even if the main transaction is rolled back.

Example:

CREATE OR REPLACE PROCEDURE log_error(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log(message, log_date) VALUES (p_msg, SYSDATE);
  COMMIT;
END;

In this example, the error logging procedure runs in an autonomous transaction, so the log entry is saved independently of the main transaction's outcome.