Autonomous Transactions
In certain situations, typically in database application error logging, the transactions have to be committed and rolled back simultaneously. In Oracle, it is done by setting up transaction as autonomous. When a main database load transaction fails, typically code calls a ROLLBACK in exception handling step. Without setting up an autonomous transaction, when a rollback occurs, it is difficult to locate and track the error, load step and reason for the error since the transaction is rolled back.
 
When a transaction is setup as autonomous, it can be called during load logging and error logging. When the main transaction encounters an error issuing a ROLLBACK, the error logging will have autonomously committed the load state and error details into logging tables.


Load/Application Logging Using Autonomous Transaction
BEGIN
  common_logging.log_app(1,'TEST','USERS',1,'Step 1: Insert','LUT','N','I',user);

  INSERT INTO some_table(col1, col2, col3)
  SELECT NULL, 100, SYSDATE FROM DUAL;
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
  common_logging.log_load_error(1,'TEST','USERS',user,1,'LUT',SQLCODE,SQLERRM,'Y');
  ROLLBACK;
END;
/
Example: In the code shown above, if col2 is created as a DATE datatype, an exception would occur resulting in ROLLBACK. As the logging is setup as an autonomous transaction, the values at the start of load and the error log details will get committed in respective tables. The autonomous transactions may be used to avoid mutating table triggers, but could result in transaction deadlocks and data integrity issues. This has been discussed on Tom Kyte's website.


Autonomous Transaction Setup
CREATE OR REPLACE PACKAGE common_logging AS

  PROCEDURE log_app(i_app_id IN common_app_log.app_log_id%TYPE,
   i_app_name IN common_app_log.application_name%TYPE,
   i_schema IN common_app_log.db_schema%TYPE,
   i_load_step IN common_app_log.load_step%TYPE,
   i_load_stat IN common_app_log.load_status%TYPE,
   i_load_code IN common_app_log.load_code%TYPE,
   i_notify IN common_app_log.notify_flag%TYPE,
   i_load_flag IN common_app_log.load_complete%TYPE,
   i_user IN common_app_log.login_user%TYPE);
  
  PROCEDURE log_load_error(i_err_id IN common_error_log.error_log_id%TYPE,
   i_app_name IN common_error_log.application_name%TYPE,
   i_schema IN common_error_log.db_schema%TYPE,
   i_user IN common_error_log.login_user%TYPE,
   i_load_step IN common_error_log.load_step%TYPE,
   i_error_num IN common_error_log.error_number%TYPE,
   i_error_cd IN common_error_log.error_code%TYPE,
   i_error_desc IN common_error_log.error_desc%TYPE,
   i_notify IN common_error_log.notify_flag%TYPE);

END common_logging;
/
CREATE OR REPLACE PACKAGE BODY common_logging As

  PROCEDURE log_app(i_app_id IN common_app_log.app_log_id%TYPE,
   i_app_name IN common_app_log.application_name%TYPE,
   i_schema IN common_app_log.db_schema%TYPE,
   i_load_step IN common_app_log.load_step%TYPE,
   i_load_stat IN common_app_log.load_status%TYPE,
   i_load_cd IN common_app_log.load_code%TYPE,
   i_notify IN common_app_log.notify_flag%TYPE,
   i_load_flag IN common_app_log.load_complete%TYPE,
   i_user IN common_app_log.login_user%TYPE) AS
  
  PRAGMA AUTONOMOUS_TRANSACTION;

  BEGIN
   INSERT INTO common_app_log
   (app_log_id, application_name, db_schema, login_user,
   load_step, load_code, load_status, load_complete, notify_flag)
   SELECT i_app_id, i_app_name, i_schema,
   NVL(i_user,SYS_CONTEXT('USERENV','OS_USER')),
   i_load_step,i_load_cd,i_load_stat,i_load_flag,i_notify
   FROM DUAL;
   COMMIT;
  EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
  END log_app;

PROCEDURE log_load_error(i_err_id IN common_error_log.error_log_id%TYPE,
   i_app_name IN common_error_log.application_name%TYPE,
   i_schema IN common_error_log.db_schema%TYPE,
   i_user IN common_error_log.login_user%TYPE,
   i_load_step IN common_error_log.load_step%TYPE,
   i_load_cd IN common_error_log.load_code%TYPE,
   i_error_num IN common_error_log.error_number%TYPE,
   i_error_cd IN common_error_log.error_code%TYPE,
   i_error_desc IN common_error_log.error_desc%TYPE,
   i_notify IN common_error_log.notify_flag%TYPE) AS

  PRAGMA AUTONOMOUS_TRANSACTION;

  BEGIN
   INSERT INTO common_error_log
   (error_log_id,application_name,db_schema,load_step,load_code,
   error_number,error_code,error_desc,login_user,notify_flag)
   SELECT NVL(i_err_id,TO_CHAR(SYSDATE,'yyyymmddhh24miss')),
   i_app_name,i_schema,i_load_step,i_load_cd,i_error_num, i_error_cd,i_error_desc,
   NVL(i_user,SYS_CONTEXT('USERENV','OS_USER')),i_notify
   FROM DUAL;
   COMMIT;
  EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
  END log_load_error;

END common_logging;
/

Back

Oracle registered trademark of Oracle Corporation.

  72928