why it is working without PRAGMA AUTONOMOUS_TRANSACTION

Question

I have a misunderstanding regarding to

PRAGMA AUTONOMOUS_TRANSACTION

directive.

As far as I know, it is used in logging or auditing procedure, to run independently by the main program (autonomous, procedure, function or trigger).

I have an UPDATE on a table which generated DUP_VAL_ON_INDEX. In this exception, I call a logging procedure which logs the error into a table. In the logging procedure I didn't specified PRAGMA AUTONOMOUS_TRANSACTION directive but it still makes the insert in my logging table.

Here is my code:

create table TEST_PRAGMA
    ( COL_1 number primary key
    , COL_2 number
    );

--
insert into TEST_PRAGMA values (1, 200);
insert into TEST_PRAGMA values (2, 200);
--
create table T_LOG    
    ( msg_num number primary key
    , MSG_DATE timestamp(6)
    , INFO_MSG varchar2(10)
    , LONG_MSG varchar2(100)
    );
--    
create sequence SEQ_TEST start with 1 increment by 1 nocache nocycle;

Package:

create or replace package pkg_logging as

    procedure PRC_LOG ( P_MSG_NUM number 
                      , P_MSG_DATE timestamp
                      , P_INFO_MSG varchar2
                      , p_long_msg varcahr2);
end PKG_LOGGING;
--
create or replace package body pkg_logging as

    procedure PRC_LOG ( P_MSG_NUM number 
                      , P_MSG_DATE timestamp
                      , P_INFO_MSG varchar2
                      , P_LONG_MSG VARCHAR2)
                      as
    begin

        insert into T_LOG
            ( MSG_NUM
            , MSG_DATE
            , INFO_MSG
            , LONG_MSG
            )
        values
            ( P_MSG_NUM 
            , P_MSG_DATE
            , P_INFO_MSG
            , P_LONG_MSG

            );
        commit;
    EXCEPTION
        when OTHERS then
            rollback;
            RAISE_APPLICATION_ERROR(-20000, 'other error has occured: ' || sqlcode || ' - ' || sqlerrm);
    end PRC_LOG;
end PKG_LOGGING;
--
set SERVEROUTPUT on;
begin

    update TEST_PRAGMA set COL_1 = 1 where COL_2 = 200;
    commit;

EXCEPTION
    when DUP_VAL_ON_INDEX then 
    dbms_output.put_line ('DUP_VAL_ON_INDEX error has occured');
        PKG_LOGGING.PRC_LOG(SEQ_TEST.NEXTVAL, systimestamp, 'error', 'test de logging');
        rollback;
end;

Because I didn't specified the PRAGMA directive, I was expecting not to log the error even if the logic is correct.

Can anyone explain me why it is still logs my error and provide a sample where it does not log the code if I do not specify the PRAGMA AUTONOMOUS_TRANSACTION directive, please?

Thank you,

Answer 1

PKG_LOGGING.PRC_LOG() has a commit statement so it will commit.

Suppose your code looked liked this:

set SERVEROUTPUT on;
begin

    insert into TEST_PRAGMA values (3, 300);
    PKG_LOGGING.PRC_LOG(SEQ_TEST.NEXTVAL, systimestamp, 'info', 'inserted a record');

    update TEST_PRAGMA set COL_1 = 1 where COL_2 = 200;
    commit;

EXCEPTION
    when DUP_VAL_ON_INDEX then 
    dbms_output.put_line ('DUP_VAL_ON_INDEX error has occured');
        PKG_LOGGING.PRC_LOG(SEQ_TEST.NEXTVAL, systimestamp, 'error', 'test de logging');
        rollback;
end;

How many records would you have in TEST_PRAGMA? Three. Because the insert was committed when we called PKG_LOGGING.PRC_LOG(), and consequently the rollback in the exception handler had no effect. And that's why we should use PRAGMA AUTONOMOUS_TRANSACTION in audit and logging routines: so we can successfully persist our logging messages without affecting the broader transaction.

So you should add PRAGMA AUTONOMOUS_TRANSACTION to PKG_LOGGING.PRC_LOG().

---

Incidentally, I think you should be careful with an error handler like this in a logging package:

EXCEPTION
    when OTHERS then
        rollback;
        RAISE_APPLICATION_ERROR(-20000, 'other error has occured: ' || sqlcode || ' - ' || sqlerrm);
end PRC_LOG;

In some situations we definitely would want to stop our process if we can't log vital information. But other times we want logging to fail gracefully. For instance I need the overnight batch run to abend if it can't record errors, because that log is my only way of knowing what - if anything - went wrong, and it's better for the whole thing not to run that for it to run incompletely and me not to know that some things failed. But if I'm just writing some trace messages in Test I might prefer the long running process to conclude without a complete set of trace rather than abend because the logging table has run out of space.

Also, using raise_application_error() is not necessary. Just issue raise; after the rollback and be done with it.

Answer 2

Can anyone explain me why it is still logs my error and provide a sample where it does not log the code if I do not specify the PRAGMA AUTONOMOUS_TRANSACTION directive, please?

The error is being Inserted in Log table since you are handling it as an Exception handling. You need to understand the behavior of AUTONOMOUS transaction as being an Independent piece of code which executes even if the main calling proc/pkg fails. It's not being handled as a part of Exception Handling. As shown in below demo you can see proc marked as AUTONOMOUS is called in BEGIN block directly rather than in Exception block to understand the behavior.

DECLARE
    l_salary   NUMBER;
--Private Proc marking as Autonomous transaction 
procedure nested_block ‬ 
   as 
   pragma AUTONOMOUS_TRANSACTION;
   BEGIN
     UPDATE emp
     SET salary=salary+15000
     WHERE emp_no=1002;
    COMMIT;
    END;‭
--Main Block    ‬
BEGIN
SELECT salary 
INTO l_salary 
FROM emp 
WHERE emp_no=1001; 

Dbms_output.put_line('Before Salary of 1001 is'||l_salary); 

SELECT salary 
INTO l_salary 
FROM emp WHERE emp_no=1002;

Dbms_output.put_line('Before Salary of 1002 is '|| 1_salary);

UPDATE emp
    SET
        salary = salary + 5000
WHERE emp_no = 1001;

--Calling Autonomous transaction
nested_block;

--And rolling back previous updates.
ROLLBACK;

SELECT salary INTO
    l_salary
FROM emp
WHERE emp_no = 1001;

dbms_output.put_line('After Salary of 1001 is'|| l_salary);

SELECT salary 
INTO  l_salary
FROM emp
WHERE emp_no = 1002;

dbms_output.put_line('After Salary of 1002 is ' || l_salary);

end;

Output:

The output will have the Update done in Autonomous transaction. Updates done in the main block will be rolledback but not the one which is done in private proc marked as Autonomous

Before Salary of 1001 is 15000 
Before Salary of 1002 is 10000 
After  Salary of 1001 is 15000 
After  Salary of 1002 is 25000