PRAGMA AUTONOMOUS_TRANSACTION:To Commit a part of Tranasaction in PL/SQL

There would be business requirement to just commit or rollback part of the transaction regardless of the actual transactions success or failure.We can handle this using PRAGMA AUTONOMOUS_TRANSACTION, if we create a block as PRAGMA AUTONOMOUS_TRANSACTION then this will be independent of thecalling block so if we Commit or rollback a transaction in this PRAGMA AUTONOMOUS_TRANSACTION block will have no commit/rollback impact on the called block.

Example:
create table pragma_table (ename varchar2(32), e_id number);
insert into pragma_table values ('','1');
insert into pragma_table values ('','2');

CREATE OR REPLACE PROCEDURE Sample_AUTONOMOUS_TRANSACTION(emp_id number)
AS PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE pragma_table SET ename = 'TWO' Where e_id = 2;
dbms_output.put_line('Iyen going to Commit');
COMMIT;
EXCEPTiON WHEN OTHERS THEN
dbms_output.put_line('Iyen in Pragma Exception so rollback' sqlerrm);
ROLLBACK;
END;


BEGIN
UPDATE pragma_table SET ename = 'ONE' Where e_id = 1; Sample_AUTONOMOUS_TRANSACTION(2);exception when others then dbms_output.put_line('Iyen Example in exception' sqlerrm);
end;

Execute the below select once with Commit and once with rollback; You can find the difference.
select * from pragma_table;

RoT: As a Rule of Thumb , always make sure that your Autonomous transaction block is either getting rollback or commit.

Do send your comments.

--Iyen