Exclude procedure call from transaction rollback

Question

How can I exclude a statement in a transaction rollback?

Begin
Insert into table a
...
...
call proc_1()
...
...
Rollback

How can I rollback all the commands except/prevent proc_1() from rolling back?

Answer 1

You can make the procedure have its on transaction - called an autonomous transaction, but be careful with this one, you can easily get yourself into deadlocks if you use it carelessly without knowing your transactions.

create or replace procedure p1 (...) as
  pragma autonomous transaction
begin
  ...
end;

Usually this is used for logging purposes.