ORA-06519: active autonomous transaction detected and rolled back

AUTONOMOUS_TRANSACTION pragma is a subprogram marked with this pragma can do SQL operations independently and its commit or roll back those operations, without committing or rolling back the data in the main transaction. Subprogram doing independent Units of Work with Autonomous Transactions.

Main advantage:
1. Autonomous transaction is work fully independent.
2. It shares no locks, resources, or commit-dependencies with the main transaction.
3. You can do work like log events, increment retry counters, and so on, even if the main transaction rolls back.

Error:
We have TEST user on PDB1 database


--create or replace procedure with Autonomous transaction
create or replace procedure update_salary
is
pragma autonomous_transaction;
begin
update test.emp set salary = salary +100 where employee_id = 100;
end;
/

ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "TEST.UPDATE_SALARY", line 6
ORA-06512: at line 6


Cause
For UPDATE_SALARY automonous_trasaction procedure which is created act as seperate transaction. It independend from main transaction.
So you need to specifiy the ROLLBACK and COMMIT for it in the procedure.

Solution
Use rollback and commit statement in the automonous_trasaction procedure/function/trigger to avoid error.

Modified the procedure with commit/rollback will execute it successfully

--create or replace procedure with Autonomous transaction
create or replace procedure update_salary
is
pragma autonomous_transaction;
begin
update test.emp set salary = salary +100 where employee_id = 100;
commit;
end;
/

--Use updates_salary prcedure in this transaction
set serveroutput on
declare
l_salary number;
begin
select salary into l_salary from emp where employee_id = 100;
dbms_output.put_line(l_salary);
update_salary;
end;
/
PL/SQL procedure successfully completed.