PL/SQL Documentation interface

During the execution of a scheduler task different events take place. The most basic events are the beginning and the end of the task.
For these events PL/SQL procedures are declared in the SO_INTERFACE package that can be customized. This package is located under the installation user; this has the advantage that the scheduler database tables can be directly accessed for extra information.

Do not use a database view for accessing data.
Only the SO_INTERFACE package body can be changed, not the package header.

All the procedures are called indirectly by the scheduler server or the scheduler task, they should not be called directly from any other PL/SQL code.
All functionality that can be used in a scheduler task like for example creating a new task output destination can be done in each interface procedure.

The code examples shown are taken from the SO_INTERFACE package included in the latest scheduler release.

Back to top 

so_interface.task_activate

The SO_INTERFACE.TASK_ACTIVATE procedure is called when a scheduler task is activated.
This is done just before the actual script procedure is executed.

procedure task_activate(
    p_task_id in integer,
    p_task_scheduled in date)
  
Parameter Description
p_task_id The scheduler task identifier.
p_task_scheduled The date and time the scheduler task is scheduled and must be activated.

The following code displays some extra information in the task output default destination.

procedure task_activate(
    p_task_id in integer,
    p_task_scheduled in date)
is
  l_task scheduler_tasks%rowtype;
  l_script scheduler_scripts%rowtype;
  l_fmt varchar2(30) := 'YYYY-MM-DD HH24:MI:SS';
  l_line constant varchar2(100) := rpad('=',70,'=');
begin

  select    *
  into      l_task
  from      scheduler_tasks t
  where     t.id = p_task_id;

  select    *
  into      l_script
  from      scheduler_scripts s
  where     s.id = l_task.script_id;

  so_run.output(l_line);
  so_run.output('Script : ' ||l_script.script_owner||'.'||
                              l_script.script_name);
  so_run.output('Task id : ' ||to_char(l_task.id));
  so_run.output('Created : '||to_char(l_task.task_created,l_fmt));
  so_run.output('Scheduled : '||to_char(p_task_scheduled,l_fmt));
  so_run.output('Activated : '||to_char(l_task.task_activated,l_fmt));
  so_run.output('Running : '||to_char(l_task.task_running,l_fmt));
  so_run.output(l_line);

  -- set some information
  dbms_application_info.set_module(l_script.script_owner||'.'||
                                   l_script.script_name||
                                   '['||to_char(l_task.id)||']',null);

end task_start;
  

Next an example what is written to the task output default destination after the scheduler task is activated.

======================================================================
Script : PLSQLSCH.SO_DEMO_SLEEP
Task id : 11990
Created : 2007-04-11 19:04:59
Scheduled : 2007-04-11 19:04:50
Activated : 2007-04-11 19:05:00
Running : 2007-04-11 19:05:06
======================================================================
  
Back to top 

so_interface.task_ready

The SO_INTERFACE.TASK_READY procedure is called when the scheduler task is ready.

The procedure is not called when a scheduler task is ready with status Unknown. In this case the task has not ended normally, always check the scheduler server log for the exact error.
procedure task_ready(
    p_task_id in number);
  
Parameter Description
p_task_id The scheduler task identifier.

The following code displays some extra information in the task output default destination.

procedure task_ready(
    p_task_id in number)
  l_task scheduler_tasks%rowtype;
  l_fmt varchar2(30) := 'YYYY-MM-DD HH24:MI:SS';
  l_line constant varchar2(100) := rpad('=',70,'=');
begin

  select    *
  into      l_task
  from      scheduler_tasks t
  where     t.id = p_task_id;

  so_run.output(l_line);
  so_run.output('Task id : ' ||to_char(l_task.id));
  so_run.output('Status : '||l_task.task_status||
     ' ('||upper(so_shared.status_task(l_task.task_status))||')');
  so_run.output('Ready : '||to_char(l_task.task_ready,l_fmt));
  so_run.output(l_line);

end task_end;
  

Next an example what is written to the default task output destination with the above code.

======================================================================
Task id : 11997
Status : F (FINISHED)
Ready : 2007-04-17 21:29:40
======================================================================
  
Back to top 

so_interface.task_overtime

The procedure SO_INTERFACE.TASK_OVERTIME is called when the scheduler task could not be activated within the overtime period and the overtime indication is Pass or Error.

Before the SO_INTERFACE.TASK_OVERTIME procedure is called the SO_INTERFACE.TASK_ACTIVATE procedure is called and afterwards the procedure SO_INTERFACE.TASK_READY.
procedure task_overtime(
    p_task_id in integer,
    p_overtime_at in date,
    p_overtime_indication in varchar2);
  
Parameter Description
p_task_id The scheduler task identifier.
p_overtime_at The date and time at which the overtime period expires.
p_overtime_indication The overtime indication code.

The following example code displays some extra information in the task output default destination.

procedure task_overtime(
    p_task_id in integer,
    p_overtime_at in date,
    p_overtime_indication in varchar2)
is
  l_fmt varchar2(30) := 'YYYY-MM-DD HH24:MI:SS';
begin
  so_run.output_box('OVERTIME PERIOD EXPIRED AT '||
                    to_char(p_overtime_at,l_fmt)||'.');
  if (p_overtime_indication = 'P')
  then
    -- the task is not started/activated and gets status P (Passed)
    so_run.output_box('OVERTIME TASK IS PASSED.');
  else
    -- the task is not started/activated and gets status E (Error)
    so_run.output_box('OVERTIME TASK GETS ERROR STATUS.');
  end if;
end task_overtime;
  

Next an example what is written to the task output default destination with the above code.
In this example the overtime period is one hour with overtime indication set to Pass.

======================================================================
Script : PLSQLSCH.SO_DEMO_TREE
Task id : 383381
Created : 2008-04-15 13:00:45
Scheduled : 2008-04-15 12:00:00
Activated : 2008-04-15 13:00:48
Running : 2008-04-15 13:00:51
======================================================================
***************************************************
*                                                 *
* OVERTIME PERIOD EXPIRED AT 2008-04-15 13:00:00. *
*                                                 *
***************************************************
****************************
*                          *
* OVERTIME TASK IS PASSED. *
*                          *
****************************
======================================================================
Task id : 383381
Status : P (PASSED)
Ready : 2008-04-15 13:00:51
======================================================================
  
Back to top 

so_interface.task_flow

The procedure SO_INTERFACE.TASK_FLOW is called when the scheduler task is part of a flow and is not activated because the flow action is Pass or Error.

Before the SO_INTERFACE.TASK_FLOW procedure is executed the SO_INTERFACE.TASK_ACTIVATE procedure is called and afterwards the procedure SO_INTERFACE.TASK_READY.
procedure task_flow(
    p_task_id in integer,
    p_flow_action in varchar2);
  
Parameter Description
p_task_id The scheduler task identifier.
p_flow_action The flow action.

The following example code displays some extra information in the default task output destination.

procedure task_flow(
    p_task_id in integer,
    p_flow_action in varchar2)
is
begin
  if (p_flow_action = 'P')
  then
    -- the task is not started/activated but gets status P (Passed)
    so_run.output_box('FLOW TASK IS PASSED.');
  else
    -- the task is not started/activated but gets status E (Error)
    so_run.output_box('FLOW TASK GETS ERROR STATUS.');
  end if;
end task_flow;
  

so_interface.task_progress

The procedure SO_INTERFACE.TASK_PROGRESS is called when PL/SQL progress documentation information must be written to a task output destination.

procedure task_progress(
    p_task_id                  in integer,
    p_destination_id           in integer,
    p_progress_total           in integer,
    p_progress_done            in integer,
    p_progress_percentage_done in integer,
    p_progress_elapsed         in integer,
    p_progress_remaining       in integer,
    p_progress_updated         in date,
    p_progress_ready           in date);
  
Parameter Description
p_task_id The scheduler task identifier.
p_destination_id A unique task output destination identifier.
A value null or zero means the default task output destination.
p_progress_total The total items of the running scheduler task that needs to be processed.
p_progress_done The total items of the running task that are processed.
p_progress_percentage_done The percentage done.
p_progress_elapsed The time elapsed in seconds.
p_progress_remaining The estimated number of seconds needed for processing the remainig items.
p_progress_updated The last date and time the progress information was updated.
p_progress_ready The estimated date and time at which all the items will be processed.

The following example code displays progress information in the task output default destination.

procedure task_progress(
    p_task_id                  in integer,
    p_destination_id           in integer,
    p_progress_total           in integer,
    p_progress_done            in integer,
    p_progress_percentage_done in integer,
    p_progress_elapsed         in integer,
    p_progress_remaining       in integer,
    p_progress_updated         in date,
    p_progress_ready           in date)
is
  l_txt varchar2(100) := null;
begin

  l_txt := lpad(to_char(p_progress_done),
                length(p_progress_total))||
           '/'||to_char(p_progress_total)||
           ' '||to_char(p_progress_percentage_done,'990')||
           '%  '||so_shared.seconds_to_string(p_progress_elapsed,'N')||
           '   ';

  if (p_progress_remaining is not null)
  then
    l_Txt := l_Txt||
             so_shared.seconds_to_string(p_progress_remaining,'N')||
             ' ('||
             to_char(p_progress_ready,'YYYY-MM-DD HH24:MI:SS')||
              ')';
  end if;

  so_run.output(p_output_text => l_txt
               ,p_destination_id => p_destination_id);

end task_progress;
  

Next an example how the scheduler task progress information is written to a task output destination with the above code.

21:40:52 Begin.
21:41:17 25/60   42%  0-00:00:25   0-00:00:35 (2007-03-19 21:41:52)
21:41:42 50/60   83%  0-00:00:50   0-00:00:10 (2007-03-19 21:41:52)
21:41:52 Ready.
  
Back to top 

so_interface.task_restart

The procedure SO_INTERFACE.TASK_RESTART is called when an already completed scheduler task is restarted. If this is the case the scheduler task does not need to be activated.

This event only occurs for child tasks for which the parent scheduler task is restarted.
Before the SO_INTERFACE.TASK_RESTART procedure the SO_INTERFACE.TASK_ACTIVATE procedure is called and afterwards the procedure SO_INTERFACE.TASK_READY.
procedure task_restart(
    p_task_id in number);
  
Parameter Description
p_task_id The scheduler task identifier.

The following example code displays some extra information in the default task output destination.

procedure task_restart(
    p_task_id in number)
is
begin
  so_run.output_box('THIS RESTARED TASK IS ALREADY COMPLETED.');
end task_restart;
  

Next an example how output with the SO_INTERFACE.TASK_RESTART together with SO_INTERFACE.TASK_ACTIVATE and SO_INTERFACE.TASK_READY procedure is written to a task output destination.

======================================================================
Script : PLSQLSCH.SO_DEMO_RESTART_SUBTASK_SUB
Task id : 383390
Created : 2008-04-15 13:45:22
Scheduled : 2008-04-15 13:45:21
Activated : 2008-04-15 13:45:23
Running : 2008-04-15 13:45:26
======================================================================
*********************************************
*                                           *
* THIS RESTARTED TASK IS ALREADY COMPLETED. *
*                                           *
*********************************************
======================================================================
Task id : 383390
Status : F (FINISHED)
Ready : 2008-04-15 13:45:26
======================================================================
  
Back to top 

so_interface.task_dbms_job_restart

The procedure SO_INTERFACE.TASK_DBMS_JOB_RESTART is called when the same scheduler task is restarted because for some reason the database session was aborted during execution.

A scheduler task is only restarted this way when the database session is aborted and the scheduler task oracle restart property is set.
procedure task_dbms_job_restart(
    p_task_id in integer);
  
Parameter Description
p_task_id The scheduler task identifier.

The following example code displays some extra information in the task output default destination.

procedure task_dbms_job_restart(
    p_task_id in integer)
is
begin
  so_run.output;
  so_run.output_box('TASK IS RESTARTED BY ORACLE AS A NEW DBMS_JOB.');
  so_run.output;
end task_dbms_job_restart;
  
Back to top 

so_interface.task_suspend

The procedure SO_INTERFACE.TASK_SUSPEND is called when a running scheduler task is suspended.

procedure task_suspend(
    p_task_id in number);
  
Parameter Description
p_task_id The scheduler task identifier.

The following example code displays some extra information in the task output default destination.
Information about who suspended the scheduler task is also written to the scheduler server log.

procedure task_suspend(
    p_task_id in number)
is
  l_task scheduler_tasks%rowtype;
  l_line constant varchar2(100) := rpad('=',70,'=');
begin

  select    *
  into      l_task
  from      scheduler_tasks t
  where     t.id = p_task_id;

  so_run.output(l_line);
  so_run.output('TASK IS SUSPENDED BY '||l_task.suspend_by||'.');

end task_suspend;
  
Back to top 

so_interface.task_unsuspend

The procedure SO_INTERFACE.TASK_UNSUSPEND is called when a suspended scheduler task is not suspended anymore and starts running again.

procedure task_unsuspend(
    p_task_id in integer,
    p_suspend_at in date,
    p_suspend_till in date);
  
Parameter Description
p_task_id The scheduler task identifier.
p_suspend_at The date and time at which the scheduler task was suspended.
p_suspend_till The date and time untill the scheduler task is suspended.
If no value is passed the suspend date is 1 january 4000.

The following example code displays some extra information in the task output default destination.

procedure task_unsuspend(
    p_task_id in integer,
    p_suspend_at in date,
    p_suspend_till in date)
is
  l_line constant varchar2(100) := rpad('=',70,'=');
  l_task scheduler_tasks%rowtype;
  l_fmt varchar2(30) := 'YYYY-MM-DD HH24:MI:SS';
begin

  select    *
  into      l_task
  from      scheduler_tasks t
  where     t.id = p_task_id;

  so_run.output();
  so_run.output('TASK UNSUSPENDED BY '||l_task.suspend_by||'.');
  so_run.output('TASK HAS BEEN SUSPENDED FROM '||
                to_char(p_suspend_at,l_fmt)||' TILL '||
                to_char(p_suspend_till,l_fmt)||'.');
  so_run.output(l_line);

end task_unsuspend;
  
Back to top 

so_interface.task_trace_enable

The SO_INTERFACE.TASK_TRACE_ENABLE procedure is called when a for a scheduler task the scheduler trace is enabled.
The procedure is called immediate after SO_INTERFACE.TASK_ACTIVATE if the scheduler task trace task property is set.
The procedure is also called when the trace is enabled with the scheduler trace set trace procedure.

procedure task_trace_enable(
    p_task_id in number);
  
Parameter Description
p_task_id The scheduler task identifier.

The following example code displays some extra information in the task output default destination.

procedure trace_task_enable(
    p_task_id in number)
is
begin
  so_run.output_box('SCHEDULER TRACE IS ENABLED.');
end trace_task;
  
Back to top 

so_interface.task_trace_disable

The SO_INTERFACE.TASK_TRACE_DISABLE procedure is called when a for a scheduler task the scheduler trace is disabled with the scheduler trace set trace procedure.

procedure task_trace_disable(
   p_task_id in number);
  
Parameter Description
p_task_id The scheduler task identifier.

The following example code displays some extra information in the task output default destination.

procedure task_trace_disable(
    p_task_id in number)
is
begin
  so_run.output('SCHEDULER TRACE IS DISABLED.');
end task_trace_disable;
  
Back to top 

so_interface.task_trace

The overloaded SO_INTERFACE.TASK_TRACE procedures are called when a for a scheduler task the scheduler trace is enabled and the same SO_RUN.TASK_TRACE procedure is called.
These procedures determine how scheduler trace information is written to a task output destination.

procedure task_trace(
    p_trace_text in varchar2,
    p_value_number in number,
    p_destination_id in integer);

procedure task_trace(
    p_trace_text in varchar2,
    p_value_varchar2 in varchar2,
    p_destination_id in integer);

procedure task_trace(
    p_trace_text in varchar2,
    p_value_date in date,
    p_destination_id in integer);

procedure task_trace(
    p_trace_text in varchar2,
    p_value_boolean in boolean,
    p_destination_id in integer);

procedure task_trace(
    p_destination_id in integer);

procedure task_trace(
    p_trace_text in varchar2,
    p_destination_id in integer);

procedure task_trace_box(
    p_trace_text in varchar2,
    p_destination_id in integer);
  
Parameter Description
p_trace_text Trace information.
p_value_number The numeric value.
p_value_date The date value.
p_value_boolean The boolean value.
p_value_varchar2 The varchar2 value.
p_destination_id A unique task output destination identifier.
A value null or zero means the default task output destination.

The following example code displays some trace information.
Only one procedure is shown, the other procedures work the same way.

procedure task_trace(
    p_trace_text in varchar2,
    p_value_varchar2 in varchar2,
    p_destination_id in integer)
is
begin
 
  if (p_value_varchar2 is null)
  then
    so_run.output(rpad('TRACE '||p_trace_text,50)||
                  ' : NULL',p_destination_id);
  else
    so_run.output(rpad('TRACE '||p_trace_text,50)||
                  ' : ['||p_value_varchar2||']',p_destination_id);
  end if;

end task_trace;
  
Back to top 

so_interface.task_trace_oracle

The SO_INTERFACE.TASK_TRACE_ORACLE procedure is called when the scheduler task trace oracle is enabled.
The procedure is called immediate after so_interface.task_activate.

procedure task_trace_oracle(p_task_id in number);
  
Parameter Description
p_task_id The scheduler task identifier.

The following example code displays some extra information in the task output default destination.

procedure task_trace_oracle(
    p_task_id in number)
is
begin
  so_run.output_box('ORACLE TRACE IS ENABLED.');
end task_trace_oracle;
  
Back to top