Middle

What is an autonomous transaction? How to use and declare it?

sobes.tech AI

Answer from AI

An autonomous transaction is an independent transaction that executes within another transaction but does not affect its state and can be committed or rolled back separately.

It is useful when you need to perform separate logging, auditing, or intermediate data storage operations without affecting the main transaction.

In Oracle, an autonomous transaction is declared using the pragma:

PRAGMA AUTONOMOUS_TRANSACTION;

Example of usage:

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; -- commit changes autonomously
END;

Thus, even if the main transaction rolls back, the error log entry will be preserved.

Previous Next