Oracle PL/SQL functionality for tasks

Package

create or replace package task_mgmt as
 --
 -- Version V0.1
 --

    function   id(name varchar2) return integer;

    procedure  begin_(name varchar2); -- return integer;

    function   cur_task return integer;

    procedure  done;
    procedure  exc;

end task_mgmt;
/

create or replace package body task_mgmt as
 --
 -- V0.1
 --

    cur_task_ integer;

    function id(name varchar2) return integer is -- {
        pragma autonomous_transaction;
        ret  integer;
    begin

        select t.id into ret
        from
           task t
        where
           t.name = id.name;

        return ret;

    exception when no_data_found then
        insert into task(name) values (id.name) returning id into ret;
        commit;
        return ret;

    end id; -- }

    procedure begin_(name varchar2) is -- {
       pragma autonomous_transaction;
    begin

       if cur_task_ is not null then
          raise_application_error(-20800, 'cannot start task if another thas is not finished');
       end if;

       insert into task_exec(start_, task_id, ses_id) values (sysdate, id(name), ses_mgmt.id) returning id into cur_task_;
       commit;

    end begin_; -- }

    procedure done is -- {
    begin

       if cur_task_ is null then
          raise_application_error(-20800, 'cur_task_ is null');
       end if;

       update task_exec t set end_ = sysdate where t.id = cur_task_;
       commit;

       ses_mgmt.ping;
       cur_task_ := null;
    end done; -- }

    function cur_task return integer is -- {
    begin
       return cur_task_;
    end cur_task; -- }

    procedure exc_(err varchar2) is -- {
       pragma autonomous_transaction;
    begin
       update task_exec t set
           t.end_ = sysdate,
           t.err  = exc_.err
        where
           t.id = cur_task_;

        commit;
    end exc_; -- }

    procedure exc is -- {
       sqlerrm_  varchar2(500) := sqlerrm;
       sqlcode_  integer       := sqlcode;
    begin

       if cur_task_ is null then
          raise_application_error(-20800, 'cur_task_ is null');
       end if;

       exc_(sqlerrm_);
       rollback;

       ses_mgmt.ping;
       cur_task_ := null;

       if sqlcode_ != -06508 then -- : PL/SQL: could not find program unit being called
          raise_application_error(-20800, 'task ended because of ' || sqlerrm_);
       end if;

    end exc; -- }

end task_mgmt;
/

Tables

create table task (
   name     varchar2(100)                    not null,
   id       integer         generated always as identity,
   --
   constraint task_pk primary key (id),
   constraint task_uq unique(name)
)
organization index;

create table task_exec (
   start_   date            default sysdate not null,
   end_     date                                null,
   err      varchar2(250)                       null,
   task_id  integer         not null,
   ses_id   integer         not null,
   id       integer         generated always as identity,
   --
   constraint task_exec_pk primary key (id  ),
   constraint task_exec_fk_task foreign key (task_id) references task,
   constraint task_exec_fk_ses  foreign key (ses_id ) references ses
);

Views

create or replace view task_exec_v as
select
   tsk.name          ,
   tim.s_ago(tsk_exec.start_) as start_s_ago,
   tim.s_ago(tsk_exec.end_  ) as end_s_ago,
   tsk_exec.err         ,
   ses.usr         ,
   ses.usr_proxy   ,
   ses.usr_os      ,
   case when tsk_exec.id  = task_mgmt.cur_task then 'y' else 'n'                  end cur_task,
   case when ses.cur_ses = 'y' then row_number() over (order by tsk_exec.id desc) end cur_ses_r,
   ses.cur_ses     ,
   ses.sid         ,
   ses.serial#     ,
   tsk_exec.start_ ,
   tsk.id        task_id,
   tsk_exec.id   id,
   ses.id    ses_id
from
   task       tsk                                      join
   task_exec  tsk_exec  on tsk.id = tsk_exec.task_id   join
   ses_v      ses       on ses.id = tsk_exec.ses_id;

Helper scripts

Test

--
-- TODO: This test must currently be executed manually (for example in SQL Developer)
--

declare
   id integer;
begin
   id := plsql_pkg_owner.task_mgmt.id('test 01');
   dbms_output.put_line('id = ' || id);

   id := plsql_pkg_owner.task_mgmt.id('test 02');
   dbms_output.put_line('id = ' || id);

   id := plsql_pkg_owner.task_mgmt.id('test 01');
   dbms_output.put_line('id = ' || id);

end;
/

begin
   plsql_pkg_owner.task_mgmt.begin_('test 02');
   plsql_pkg_owner.log_mgmt.msg('foo');
   plsql_pkg_owner.log_mgmt.msg('bar');
   plsql_pkg_owner.log_mgmt.msg('baz');
   plsql_pkg_owner.task_mgmt.done;
end;
/

select * from plsql_pkg_owner.task_exec;
select * from plsql_pkg_owner.task_exec_v;

select * from plsql_pkg_owner.log_v where cur_ses = 'y';

See also