PL/SQL Documentation progress

Progress information in a running scheduler task is very usefull if scheduler users want to known how long a certain operation will take.
Progress information is displayed on the task properties window (progress panel) and available on the database view SO_VW_TASKS. Progress information can also be written to any task output.

Screenshot PL/SQL scheduler window W25 Task properties (progress panel)

Progress information is build into the PL/SQL program that is executed. In the program two types of information must be available.

  1. The total number of items that have to be processed.
  2. How many items are processed.

The total number of items is needed to initialize the progress information.
After initialization each time one or more items are processed a procedure must be called that updates the progress information.

Back to top 

Progress initialization

The procedure SO_RUN.PROGRESS_INIT initializes the progress information.

procedure progress_init(
    p_progress_total    in integer,
    p_progress_interval in integer default 0,
    p_destination_id    in integer default null);
  
Parameter Description
p_progress_total The total items of the running scheduler task that needs to be processed.
p_progress_interval The interval in which progress information is written to a task output destination.
If the value is less or equal zero no progress information is written.
p_destination_id A unique task output destination identifier.
A value null or zero means the default task output destination.
Error Description
OSC-10065
SCHEDULER FUNCTIONALITY CAN ONLY BE USED WITHIN A RUNNING TASK
OSC-10072
INVALID DESTINATION IDENTIFIER "%1"
Back to top 

Update progress information

Two procedures exist for updating the progress information by adjusting the number of items that are processed.
Procedure SO_RUN.PROGRESS_INCREMENT increments the processed items with one and procedure SO_RUN.PROGRESS_UPDATE sets the number of processed items.
These two procedures can be used together. If the increment procedure is called after the update the latest update value is incremented.

procedure progress_increment(
    p_progress_output in boolean default true);

procedure progress_update(
    p_progress_done   in integer,
    p_progress_output in boolean default true);
  
Parameter Description
p_progress_done The total items of the running task that are processed.
p_progress_output If this parameter has the value false no progress information is written to the task output destination.
If no task output destination is given during initialization the parameter is ignored.
Error Description
OSC-10065
SCHEDULER FUNCTIONALITY CAN ONLY BE USED WITHIN A RUNNING TASK
Back to top 

Clear progress information

When no progress information is needed anymore it is erased with the procedure SO_RUN.PROGRESS_CLEAR.

procedure progress_clear;
  
Error Description
OSC-10065
SCHEDULER FUNCTIONALITY CAN ONLY BE USED WITHIN A RUNNING TASK

After a clear the progress information can again be initialized.

Back to top 

Progress program example

The next progress example is implemented in the SO_DEMO package and is included in the scheduler progress demo and can be started by submitting script demo script SO DEMO PROGRESS INFO.

  procedure progress_info(
    p_total in integer,
    p_interval in integer)
  is
    l_total integer := p_total;
  begin
    -- (1) initialize status first loop
    so_run.output('Begin.');
    so_run.progress_init(
        p_progress_total => p_total,
        p_progress_interval => p_interval);

    while (l_total > 0)
    loop
      l_total := l_total - 1;
      dbms_lock.sleep(seconds => 1);

      -- (2) update progress information
      so_run.progress_increment;

    end loop;

    -- (2) clear status
    so_run.progress_clear;
    so_run.output('Ready.');
  end progress_info;
  
  1. Initialize the number of items and a interval for writing progress info to the task output default destination.
    On the progress panel only the total is displayed. No estimated date and time is calculated yet when the items are ready.
  2. Update the number of items processed.
    Each time the progress information is updated a new estimated end date and time is calculated.
  3. Clear all progress information.

Next an example is given of what is written to the task output destination.

How the progress information is written to the task output destination is defined in the scheduler interface progress procedure.

The total items are sixty and the interval is 25. The following columns are shown.

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.
  

The same information on the task properties window (progress panel) can be queried with the database view SO_VW_TASKS.

select progress_total
,      progress_done
,      progress_percentage_done
,      progress_done
,      progress_elapsed
,      progress_elapsed_text
,      progress_remaining
,      progress_remaining_text
,      progress_updated
,      progress_ready
from so_vw_tasks
where task_id = .....
  
Back to top 

Progress interface

Each time the progress information is updated and the progress information must be written to a destination the scheduler interface progress procedure is called.
In this procedure the code can be found how the progress information is written to the task output destination.

Back to top