Autonomous Transaction in Oracle Pl/SQL

Autonomous transaction is an independent transaction that can be called from any other transaction.
Let suppose a PL/SQL procedure A is calling another autonomous procedure B then procedure A will start a transaction and then when it will call the procedure B a separate transaction will be created. B will complete and return back to procedure A where it will join the same transaction which A has initiated. From SOA perspective we have "RequiresNew" i.e. create a new transaction for the sub process. This is helpful in cases for error handling.

Lets take a case, you have an error handling procedure which should be called whenever an error occurs, this should be independent procedure as this will be committed ir-respective of the main procedure. So the flow will be -whenever there is an issue in the main procedure the error handling procedure will be called and committed.

Let's first create a Error table

CREATE TABLE error_table (LOG_TIMESTAMP timestamp not null,error_details VARCHAR2(1000));

Now create a procedure to log error details in the error table

CREATE OR REPLACE PROCEDURE log_errors(p_errmsg IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_table(log_timestamp,error_details)
VALUES (SYSTIMESTAMP, p_errmsg);
COMMIT;
end;
/

as you can notice this is running in a separate transaction as we have used PRAGMA AUTONOMOUS_TRANSACTION

Now we will try to create a procedure and will raise an error in the procedure to see if this block commit the error to the error table even though the commit doesn't happen in the main procedure.

I created a anonymous block and intentionally raised an error

declare
EMPID EMPLOYEES.EMPLOYEE_ID%type;
Emp_ID EMPLOYEES.EMPLOYEE_ID%type;
begin
Select department_id into Emp_ID from employees where Employee_id=:Emid;
EXCEPTION
WHEN OTHERS THEN
log_errors(p_errmsg => SQLERRM);
ROLLBACK;
end;
/

I tested this query and passed employee id as 00

the block got executed and the error is capture in the error table.