Logging a procedure's progress using autonomous transactions in PL/SQL

create table msg_type (
  tp          varchar2(3),
  dsc         varchar2(40),
  constraint  pk_msg_type primary key (tp)
);

insert into msg_type (tp,dsc) values
  ('err', 'Error');

insert into msg_type (tp,dsc) values
  ('prg', 'Progress');

insert into msg_type (tp,dsc) values
  ('dbg', 'Debug');

insert into msg_type (tp,dsc) values
  ('msg', 'Message');

create table msg_source (
  src         varchar2(3),
  dsc         varchar2(40),
  constraint  pk_msg_source primary key (src)
);

insert into msg_source (src,dsc) values
  ('pfc', 'Upload Peformance');
insert into msg_source (src,dsc) values
  ('fsr', 'Upload Fund Shr');

insert into msg_source (src,dsc) values
  ('sr', 'Upload Fund Shr SR');

create table msg_head (
  dt          date         not null,
  id          number       not null,
  tp                       not null,
  src                      not null,
  cd          varchar2(30) not null,
  sql_cd      number,
  constraint pk_msg_hd     primary key (id),
  constraint fk_msg_hd_tp  foreign key (tp) 
    references msg_type,
  constraint fk_msg_hd_src foreign key (src)
    references msg_source
) storage (pctincrease 0 initial 512k next 512k);

create sequence seq_msg_head start with 1 increment by 1;

create or replace trigger ai_msg_hd 
  before insert on msg_head
  for each row
  begin
    if :new.id is null then
      select seq_msg_head.nextval into :new.id from dual;
    end if;
      
    select sysdate into :new.dt from dual;
  end;
/

create table msg_tail (
  id                   not null,
  description          varchar2(100),
  constraint fk_er_tl foreign key (id) 
     references msg_head
     on delete cascade
) storage (pctincrease 0 initial 512k next 512k);

create or replace package msg as
  p_def_src  msg_source.src%type;
  p_hd_id    msg_head.id%type;

  procedure id(v_id in number);
  procedure tp(
    v_tp in msg_head.tp%type,
    tail in boolean default true);
  procedure clean;

  procedure head(
    c in msg_head.cd%type,
    t in msg_head.tp%type,
    s in msg_head.src%type default p_def_src);
 
  -- sql_err is similar to the procedure head 
  -- in that it writes an entry into msg_head. However,
  -- it differs in the following ways: it writes sql_code
  -- and writes an entry with the error msg into err_tail
  procedure sql_err(
    c in msg_head.cd%type,
    s in msg_head.src%type default p_def_src,
    t in msg_head.tp%type default 'err');

  procedure tail(
    d in msg_tail.description%type);

  -- This procedure shows and deletes the
  -- next v_n entries of msg_head
  procedure nxt(v_n in number default 1);
 
  -- This procedure shows the last v_n entries
  -- in msg_head
  procedure lst(v_n in number default 1);

  -- This procedure shows the first v_n entries
  -- in msg_head
  procedure fst(v_n in number default 1);

  procedure def_src(v_def_src in msg_source.src%type);

end;
/

create or replace package body msg as
  procedure tp(
      v_tp in msg_head.tp%type,
      tail in boolean default true) is
    cursor c(v_tp in msg_head.tp%type) is
      select id from msg_head where tp=v_tp
        order by id;
  begin
    for r in c(v_tp) loop
      id(r.id);
    end loop;
  end;


  procedure id(v_id in number) is
    v_cd   msg_head.cd%type;
    cursor c(v_id in number)
      is select description from msg_tail where
        id = v_id;
  begin
    select cd into v_cd from msg_head
      where id=v_id;
    dbms_output.put_line('');
    dbms_output.put_line('  [' ||
     to_char(v_id,'999999999') || '] ' || v_cd);

    for r in c(v_id) loop
      dbms_output.put_line ('               ' || r.description);
    end loop;
  exception
    when no_data_found then
      dbms_output.put_line ('  # no entry for ' || v_id);
  end;  

  procedure clean is 
  begin
    execute immediate 
      'alter table msg_tail modify constraint fk_er_tl disable';
    execute immediate 'truncate table msg_tail';
    execute immediate 'truncate table msg_head';
    execute immediate 
     'alter table msg_tail modify constraint fk_er_tl enable';
  end;

  procedure nxt(v_n in number default 1) is
    cursor c is 
     select id from msg_head order by id for update;
    a_n number:=0;
   
  begin
    for r in c loop
      exit when a_n >= v_n;
      id(r.id);
      delete msg_head where current of c;
      a_n := a_n + 1;
    end loop;
    commit;
  end;

  procedure sql_err(
    c in msg_head.cd%type,
    s in msg_head.src%type default p_def_src,
    t in msg_head.tp%type  default 'err')
  is
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_sqlcode       number;
    v_sqlerrm       varchar2(200);
  begin
    v_sqlcode := sqlcode;
    v_sqlerrm := sqlerrm(v_sqlcode);

    insert into msg_head(cd,tp,src,sql_cd)
      values (c,t,s,v_sqlcode) returning id into p_hd_id; 
--    commit;
    tail(v_sqlerrm);
end;


  procedure head(
    c in msg_head.cd%type,
    t in msg_head.tp%type,
    s in msg_head.src%type default p_def_src)
  is
    PRAGMA AUTONOMOUS_TRANSACTION;
  begin
    insert into msg_head(cd,tp,src) values (c,t,s) 
      returning id into p_hd_id;
    commit;
  end;

  procedure tail(d in msg_tail.description%type) is
    PRAGMA AUTONOMOUS_TRANSACTION;
  begin
    insert into msg_tail(id,description) values (p_hd_id,d);
--    commit;
  end;

  procedure lst(v_n in number default 1) is
    stmt varchar2(200);
    type rct is ref cursor;
    c    rct;
    v_id number;

  begin
    stmt := 'select id from (
        select id, rank() over(order by id desc) rn
        from msg_head
      ) where rn <= :n order by id';

      open c for stmt using v_n;
      loop
        fetch c into v_id;
        exit when c%notfound;

            id(v_id);
    end loop;
  end;
 
  procedure fst(v_n in number default 1) is
    stmt varchar2(200);
      type rct is ref cursor;
      c    rct;
      v_id number;
  begin
    stmt := 'select id from (
        select id, rank() over(order by id) rn
        from msg_head where src = :1
      ) where rn <= :2 ';

      open c for stmt using p_def_src, v_n;
      loop
        fetch c into v_id;
        exit when c%notfound;

              id(v_id);
    end loop;

  end;

  procedure def_src(v_def_src in msg_source.src%type)
  is
  begin
    p_def_src := v_def_src;
  end;

end;
/

create table d (a number, b number, c number);
insert into d values(25,5,null);
insert into d values(64,4,null);
insert into d values(81,3,null);
insert into d values(17,0,null);
insert into d values(28,2,null);

commit;

declare
  cursor cr is select a, b from d;
begin
  msg.def_src('tst');
  for r in cr loop
    msg.head('updating d','prg');
    msg.tail('a: ' || r.a);
    msg.tail('b: ' || r.b);
    update d set c = r.a/r.b  where a=r.a and b=r.b;
  end loop;
exception
   when others then
      msg.sql_err('sql error');
      msg.tail('tail');  
      rollback;
	end;
     /

     select * from d;

     drop table d;