PRAGMA AUTONOMOUS_TRANSACTION in PL/SQL

To understand this, do the pl/sql below

SQL> declare
2 procedure insert_to_table_a is
3 begin
4 insert into table_a values (‘A’);
5 commit;
6 end;
7 begin
8 insert into table_b values(‘B’);
9 insert_to_table_a;
10 rollback;
11 end;
12 /

PL/SQL procedure successfully completed.

SQL> select * from table_a;

CO
–
A

SQL> select * from table_b;

CO
–
B

COMMIT at line no 5 , commits the transaction at line-no 4 and line-no 8.
Let us see the following example using PRAGMA AUTONOMOUS_TRANSACTION.

SQL> declare
2 procedure insert_to_table_a is PRAGMA AUTONOMOUS_TRANSACTION;
3 begin
4 insert into table_a values (‘A’);
5 commit;
6 end;
7 begin
8 insert into table_b values(‘B’);
9 insert_to_table_a;
10 rollback;
11 end;
12 /

PL/SQL procedure successfully completed.

SQL> select * from table_a;

CO
–
A

SQL> select * from table_b;

no rows selected

So, with the PRAGA AUTONOMOUS_TRANSACTION, the transaction state maintained independently. Commit/Rollback of nested transaction will no effect the other transaction