Oracle PL/SQL: Logger procedure with PRAGMA AUTONOMOUS_TRANSACTION

Log Table

drop table tq84_log_table purge;

create table tq84_log_table (
   txt     varchar2(200)     not null,
   id      number(10)        generated by default on null as identity primary key,
   ts      timestamp         default systimestamp
);

Logging Procedure

create or replace procedure tq84_log(t varchar2) as
   pragma autonomous_transaction;
begin
   insert into tq84_log_table(txt) values (t);
   commit;
end tq84_log;
/

Log View

create or replace view tq84_log_view as
select
   txt,
   extract(day    from (systimestamp - ts)) days_ago   ,
   extract(hour   from (systimestamp - ts)) hours_ago  ,
   extract(minute from (systimestamp - ts)) minutes_ago,
   extract(second from (systimestamp - ts)) seconcs_ago,
   id
from
   tq84_log_table
order by
   id desc;

See also