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
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
    l_total integer;
    l_done integer;

    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

    while (l_done < l_total)

      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

    end loop;

    -- (3) disable suspend option

    -- clear progress information

  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