Force chain to start immediately after completion instead of on a timer

Question

Is it possible to force an Oracle chain to restart immediately after it finishes executing, instead of on a timer? I have only found ways to do a temporal restart, which doesn't really work for me as this is a data warehouse that has varying times of completion and cannot be set to just run every X minutes.

Answer 1

The easiest way to do this is to add another step prior to the end of the chain that changes the job to start execution of the chain at SYSDATE + 1/1440.

Something like this:

CREATE OR REPLACE PROCEDURE move_next_execution
AS
BEGIN
    -- change to the correct job_id
    dbms_job.next_date(666,'sysdate+1/1440');
    -- need to commit so that the scheduler (which runs in a different session) can see it
    commit;
END;
/

sys.dbms_scheduler.create_program(
    program_name => 'PHIL.MOVE_EXEC',
    program_action => 'PHIL.move_next_execution',
    program_type => 'STORED_PROCEDURE',
    number_of_arguments => 0,
    comments => NULL,
    enabled => FALSE);

sys.DBMS_SCHEDULER.ENABLE(name=>'PHIL.MOVE_EXEC');

(Untested, but will test it when I have time - unsure if the end of the chain & job will again modify the next_date.)

I have tested the following, which works OK (but involves creating a new job):

-- schedule next execution
CREATE OR REPLACE PROCEDURE schedule_next_execution
AS
BEGIN
    -- not that job_name will have to be generated so it's unique each time
    DBMS_SCHEDULER.CREATE_JOB (
      job_name        => 'chain_job',
      job_type        => 'CHAIN',
      job_action      => 'CHAIN',
      start_date => systimestamp + interval '1' minute,
      enabled         => TRUE);

END;
/


-- just an example proc
CREATE OR REPLACE PROCEDURE do_nothing_for_2minutes
AS
BEGIN
  DBMS_LOCK.SLEEP(120);
END;
/


BEGIN
    sys.dbms_scheduler.create_program(
        program_name => 'PHIL.NOTHING',
        program_action => 'PHIL.do_nothing_for_2minutes',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'PHIL.NOTHING');   

    sys.dbms_scheduler.create_program(
        program_name => 'PHIL.SCHEDULE',
        program_action => 'PHIL.schedule_next_execution',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'PHIL.SCHEDULE');
END;
/

BEGIN
    sys.dbms_scheduler.create_chain(
        comments => 'example chain',
        chain_name => 'PHIL.CHAIN'
    );

    sys.dbms_scheduler.enable(name=>'PHIL.CHAIN');
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        STEP_NAME  => 'STEP1',
        PROGRAM_NAME => 'PHIL.NOTHING'  );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        STEP_NAME  => 'STEP2',
        PROGRAM_NAME => 'PHIL.SCHEDULE'  );
END;
/


BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        condition => 'TRUE',
        action => 'START STEP1'
        );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        condition => 'STEP1 SUCCEEDED',
        action => 'START STEP2'
        );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        condition => 'STEP2 SUCCEEDED',
        action => 'END'
        );
END;
/

-- get timestamp
select systimestamp from dual;

-- run for first time
BEGIN
  DBMS_SCHEDULER.RUN_CHAIN (
    chain_name   => 'CHAIN',
    start_steps  => null );
END;
/

-- wait 2 mins
select DBMS_LOCK.SLEEP(120) from dual;

-- check for new job
select start_date from dba_jobs where job_name = 'CHAIN_JOB';