PL/SQL Documentation suspend

Befor a scheduler task can be suspended a few lines of code must be added to the PL/SQL program that is executed.
The advantage is that the point where a program is suspended is determined by the executed program itself; this way suspending a scheduler task while it is processing a critical part can be avoided.

Task suspend information is shown on the task properties window (suspend panel) and can be queried with the database view SO_VW_TASKS.

Screenshot PL/SQL scheduler window W25 Task properties (suspend panel) Back to top 

Enable suspend mode

Only the procedure SO_RUN.SUSPEND is needed for enabling and disabling the suspend mode.
Each time the procedure is called the scheduler task will check if it needs to be suspended. If not it will continue processing.
After a scheduler task has been suspended every five seconds a check is done if it must stay suspended.

procedure suspend(
    p_suspend_enabled in boolean default true);
  
Parameter Description
p_suspend_enabled If true the suspend mode is enabled for the running scheduler task.
If the parameter has the value false the suspend mode is disabled and the scheduler task cannot be suspended anymore.
Error Description
OSC-10065
SCHEDULER FUNCTIONALITY CAN ONLY BE USED WITHIN A RUNNING TASK
Back to top 

Suspend program example

This example can be found in the SO_DEMO package and is included in the scheduler suspend demo. The example can be started by submitting demo script SO DEMO SUSPEND.
The code contains also some procedures for displaying PL/SQL progress documentation.

  procedure suspend
  is
    l_total integer;
    l_done integer;
  begin

    l_total := 120;
    l_done  := 0;

    -- display progress information
    so_run.progress_init(p_progress_total => l_total
                        ,p_progress_interval => 25);

    -- (1) enable suspend option
    so_run.suspend;

    while (l_done < l_total)
    loop

      dbms_lock.sleep(seconds => 1);

      l_done := l_done + 1;
      so_run.progress_update(p_progress_done => l_done);

      -- (2) check if task has to be suspended
      so_run.suspend;

    end loop;

    -- (3) disable suspend option
    so_run.suspend(false);

    -- clear progress information
    so_run.progress_clear;

  end suspend;
  
  1. The suspend mode is enabled and from now on the scheduler task can be suspended by any scheduler user who has access to this task.
  2. Each time at this point in the program a check is done if the scheduler task needs to be suspended.
  3. The suspend mode is disabled.

All suspend information for a given task can be queried with the database view SO_VW_TASKS.

select suspend_status
,      suspend_status_extended
,      suspend_at
,      suspend_by
,      suspend_last_check
,      suspend_request
,      suspend_time
,      suspend_time_text
,      suspend_till
,      suspend_checks
from so_vw_tasks
where task_id = .....
  
Back to top 

Suspend interface

In case a scheduler task is suspended the scheduler interface suspend procedure is called.
When the scheduler task is not suspended anymore the scheduler interface unsuspend procedure is called.
The interface procedures can be used for extra actions like writing information to a task output destination.

Back to top