drop table logtab;
create table logtab
(
    username    varchar2(30)    ,
    log_msg     varchar2(300)   ,
    stacktrace  varchar2(2000)  ,
    logtime     timestamp
);
--
--
create or replace procedure writelog
(
    p_logmsg        in  varchar2,
    p_stacktrace    in  varchar2
)
authid definer
is
    pragma autonomous_transaction;
begin
    <<bk>>
    declare
        tstamp  logtab.logtime%type;
    begin
        bk.tstamp := systimestamp;
        --
        insert into 
            logtab
            (
                username    , 
                log_msg     , 
                stacktrace  , 
                logtime
            ) 
            values 
            (
                user        , 
                p_logmsg    , 
                p_stacktrace, 
                bk.tstamp
            );
        commit;
    end;
end writelog;
/
show errors;

begin
    writelog(. . .);
    update students
    set . . . 
    where schooltype ='high school';

    writelog(. . .);
    update students
    set . . .
    where schooltype ='middle school';
exception:
    writelog(. . .);
    . . .
end;
/

create table tmptab(numval number);
--
--
insert into tmptab(numval) 
    (select rownum from all_objects where rownum <= 10);
commit;
--
select t1.numval from tmptab t1;
--
begin
    writelog(p_logmsg=>'update 4 to 40', p_stacktrace=>null);
    update tmptab t1
    set t1.numval = 40
    where t1.numval = 4;
--     
    writelog(p_logmsg=>'update 9 to 90', p_stacktrace=>null);
    update tmptab t1
    set t1.numval = 90
    where t1.numval = 9;
end;
/
--
rollback;
--
select t1.numval from tmptab t1;

Table created.


10 rows created.


Commit complete.


    NUMVAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.


PL/SQL procedure successfully completed.


Rollback complete.


    NUMVAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.