Tuesday, October 16, 2007

autonomous transaction

Autonomous transactions are commonly used by error logging routines, where the error messages must be preserved, regardless of the the commit/rollback status of the transaction. For example, the following table holds basic error messages.

CREATE TABLE error_logs (
id NUMBER(10) NOT NULL,
log_timestamp TIMESTAMP NOT NULL,
error_message VARCHAR2(4000),
CONSTRAINT error_logs_pk PRIMARY KEY (id)
);

CREATE SEQUENCE error_logs_seq;
We define a 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 (id, log_timestamp, error_message)
VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
COMMIT;
END;
/

For more info:
http://orafaq.com/node/1915

http://www.oracle-base.com/articles/misc/AutonomousTransactions.php

No comments: