Oracle PL/SQL: Logger procedure with PRAGMA AUTONOMOUS_TRANSACTION

Log Table

create table tq84_log_table (
   txt     varchar2(4000)    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) authid definer 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;

Cleaning up

drop view      tq84_log_view;
drop table     tq84_log_table;
drop procedure tq84_log;

See also