PL/SQL Documentation restart

During executing of a scheduler task restart values can be set and read.
If a scheduler task is restarted the restart values are copied from the original task to the new restarted task.

The restart values are deleted when the task is ready with status Finished.

Set and get restart values

The next three procedures in the SO_RUN package are available for setting restart values.

procedure set_restart_number(
    p_restart_name in varchar2,
    p_restart_value in number);

function get_restart_number(
    p_restart_name in varchar2)
return number;

procedure set_restart_varchar2(
    p_restart_name in varchar2,
    p_restart_value in varchar2);

function get_restart_varchar2(
    p_restart_name in varchar2)
return varchar2;

procedure set_restart_date(
    p_restart_name in varchar2,
    p_restart_value in date);

function get_restart_date(
    p_restart_name in varchar2)
return date;
  
Parameter Description
p_restart_name The name of the restart value.
The name is not case sensitive.
p_restart_value The restart value.

The above procedures are not overloaded; if for example the SO_RUN.SET_RESTART_VARCHAR2 is used the SO_RUN.GET_RESTART_VARCHAR2 must be called to get the value back.
This also applies for the procedures SO_RUN.SET_RESTART_NUMBER and SO_RUN.SET_RESTART_BOOLEAN.

When trying to get a restart value for which no value is set the value NULL is returned.
Back to top 

Restart example

The restart example can be found in the SO_DEMO package and is included in the scheduler restart main demo. The example can be started by submitting demo script SO DEMO RESTART MAIN.
Because restart values are deleted when a scheduler task finishes succesfully an exception is raised twice in this demo to simulate a crash; the task has to be restarted twice before it finally finishes succesfull.

  procedure restart_main
  is
    l_name varchar2(10) := 'DONE';
    l_crash integer := 7;
    l_total integer := 15;
    l_done integer;
  begin

    -- (1) get the restart value (or null the first time)
    l_done := nvl(so_run.get_restart_number(p_restart_name => l_name),0);

    so_run.output(p_text => 'TOTAL      '||to_char(l_total));
    so_run.output(p_text => 'START WITH '||to_char(l_done));

    while (l_done <= l_total)
    loop

      -- (2) simulate crash
      l_crash := l_crash - 1;
      if (l_crash = 0)
      then
        so_run.output('ERROR');
        raise no_data_found;
      end if;

      -- (3) set records done
      l_done := l_done + 1;
      so_run.set_restart_number(p_restart_name => l_name
                               ,p_restart_value => l_done);

      -- (4) commit the restart values to the database
      commit;

      -- (5) some information to the default output destination
      so_run.output(p_text => 'DONE '||to_char(l_done));

    end loop;

    so_run.output(p_text => 'FINISHED');

  end restart_main;
  

The first task is scheduled the normal way.
Because the restart variable DONE is read the first time at (1) and no restart value is set yet the function SO_RUN.GET_RESTART_NUMBER will return the value NULL.
An exception is raised at (2) after six times.
The task output for the first task should look like:

==============================================================================
Script : PLSQLSCH.SO_DEMO_RESTART_MAIN
Task id : 15555
Created : 2007-05-17 17:24:27
Scheduled : 2007-05-17 17:24:22
Activated : 2007-05-17 17:24:28
Running : 2007-05-17 17:24:35
==============================================================================
TOTAL      15
START WITH 0
DONE 1
DONE 2
DONE 3
DONE 4
DONE 5
DONE 6
ERROR
SCHEDULER ERROR EXECUTE : ORA-01403: no data found
==============================================================================
Task id : 15555
Status : E (ERROR)
Ready : 2007-05-17 17:24:36
==============================================================================
  

When the task is restarted the restart variable DONE will have value six.

==============================================================================
Script : PLSQLSCH.SO_DEMO_RESTART_MAIN
Task id : 15667
Created : 2007-05-17 17:32:39
Scheduled : 2007-05-17 17:24:22
Activated : 2007-05-17 17:33:55
Running : 2007-05-17 17:33:59
==============================================================================
TOTAL      15
START WITH 6
DONE 7
DONE 8
DONE 9
DONE 10
DONE 11
DONE 12
ERROR
SCHEDULER ERROR EXECUTE : ORA-01403: no data found
==============================================================================
Task id : 15667
Status : E (ERROR)
Ready : 2007-05-17 17:34:00
==============================================================================
  

The restart variable DONE will now have value twelve.
The restart of the second task (notice that the first task cannot be restarted anymore) will end succesfully.

==============================================================================
Script : PLSQLSCH.SO_DEMO_RESTART_MAIN
Task id : 15713
Created : 2007-05-17 17:36:28
Scheduled : 2007-05-17 17:24:22
Activated : 2007-05-17 17:38:31
Running : 2007-05-17 17:38:36
==============================================================================
TOTAL      15
START WITH 12
DONE 13
DONE 14
DONE 15
FINISHED
==============================================================================
Task id : 15667
Status : F (FINISHED)
Ready : 2007-05-17 17:38:36
==============================================================================
  

Because the restart values are deleted when a tasks is ready with Finished a restart of the last third task is the same as scheduling a new task.

Back to top