Explain autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION) in PL/SQL

How it works

PRAGMA AUTONOMOUS_TRANSACTION marks a subprogram's transaction as independent of the calling transaction — it gets its own COMMIT/ROLLBACK, unaffected by (and not affecting) whatever the caller later does with its own transaction. The classic use is error or audit logging that must survive even if the calling transaction later rolls back; every autonomous transaction block must explicitly COMMIT or ROLLBACK before returning, or it raises an error.

CREATE OR REPLACE PROCEDURE log_error(p_message VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (message, logged_at) VALUES (p_message, SYSDATE);
  COMMIT; -- required before returning from an autonomous transaction
END;
/
-- Even if the caller's own transaction later rolls back, this log row stays.

Related Questions