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

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