Blog

Here is an example how to use autonomous_transaction for logging purposes.

In case of rollback only the main transaction is affected.

declare

    l_categories_count      varchar2(100);
    process_error            Exception;
    
    procedure log(p_message varchar2) is 
        pragma autonomous_transaction;
    begin
        insert into APPS.LOG_ACTIONS(
            LOG_ACTION_ID,
            LOG_ACTION,
            LOG_DATE)
        values(
            LOG_ACTIONS_SEQ.NEXTVAL,
            p_message,
            sysdate              
        );
        
        if sql%rowcount = 1 then
            dbms_output.put_line('logged: ' || p_message); 
        end if;
        commit;       
    end log;
begin
    savepoint "start";
    
    insert into XXSP_ITEM_CATEGORIES
(
      ITEM_CATEGORY_ID,
      ITEM_CATEGORY_DESC,
      CREATED,
      CREATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY
  )
  values(
      xxsp_item_categories_seq.nextval,
      'potato chips',
      sysdate,
      1,
      sysdate,
      1);
      --logging 1:
      log(p_message => 'Inserted 1 Into xxsp_item_categories');
      
      --something went wrong: Raise exception:
      raise process_error;
      
      commit;
exception
    when process_error then  
        log(p_message => 'insert_error occured');        
        select count(1)
        into l_categories_count
        from xxsp_item_categories;
        
        dbms_output.put_line('Total Item Categories Inserted: ' || l_categories_count);
        dbms_output.put_line('rolling back...');
        rollback to "start";
        log(p_message => 'Rolled back');
end;

If we run the following commands, we get:

select * from log_actions order by 1
6    Inserted 1 Into xxsp_item_categories    16/4/2014 1:25:23 πμ
7    insert_error occured    16/4/2014 1:25:23 πμ
8    Rolled back    16/4/2014 1:25:23 πμ

select * from xxsp_item_categories
--NO ROWS

Objects creation for reference:

CREATE TABLE APPS.LOG_ACTIONS
(
  LOG_ACTION_ID  NUMBER(10),
  LOG_ACTION     VARCHAR2(100 BYTE),
  LOG_DATE       DATE
);

CREATE TABLE APPS.XXSP_ITEM_CATEGORIES
(
  ITEM_CATEGORY_ID    NUMBER,
  ITEM_CATEGORY_DESC  VARCHAR2(100 BYTE),
  CREATED             DATE,
  CREATED_BY          NUMBER,
  LAST_UPDATE_DATE    DATE,
  LAST_UPDATED_BY     NUMBER
);

CREATE SEQUENCE APPS.XXSP_ITEM_CATEGORIES_SEQ
  START WITH 7
  MAXVALUE 9999999999999999999999999999
  MINVALUE 0
  NOCYCLE
  NOCACHE
  NOORDER;

CREATE SEQUENCE APPS.LOG_ACTIONS_SEQ
  START WITH 21
  MAXVALUE 9999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;