Thanks for the question, Olivier.

Asked: January 18, 2016 - 7:34 pm UTC

Last updated: April 08, 2018 - 6:29 am UTC

Version: 11.2.0.4

Viewed 10K+ times! This question is

You Asked

Hi,

I have a job (JOB1) which runs every night at 0:30.
I have another job (JOB2) which must be run after JOB1 completes but only once a week, on monday.
For the moment, I manage this by starting JOB2 on monday at 4:00 because most of the time JOB1 has ended at this time.
It works but I would prefer to start JOB2 as soon as JOB1 has finished.
I imagine I can manage this with a job chain but I can't find how to do it.
In summary, I want JOB2 to start on two conditions : JOB1 has finished and we are on monday.

Here is a very basic test case :

CREATE PROCEDURE proc1
IS
BEGIN
  NULL;
END;
/
CREATE PROCEDURE proc2
IS
BEGIN
  NULL;
END;
/
BEGIN
  dbms_scheduler.create_job 
    (
    job_name => 'JOB1',
    job_type => 'STORED_PROCEDURE', 
    job_action => 'proc1', 
    repeat_interval=>'FREQ=DAILY;BYHOUR=0;BYMINUTE=30;BYSECOND=0',
    enabled => TRUE,
    auto_drop => FALSE
    );
END;
/
BEGIN
  dbms_scheduler.create_job
    (
    job_name => 'JOB2',
    job_type => 'STORED_PROCEDURE', 
    job_action => 'proc2', 
    repeat_interval => 'FREQ=WEEKLY;BYDAY=MON;BYHOUR=4;BYMINUTE=0;BYSECOND=0',
    auto_drop => FALSE,
    enabled => TRUE
    );
END;
/

Thanks in advance.

Regards,

Olivier

and Connor said...

OK, the "quick and dirty" way is for your second procedure to query dba_scheduler_job_log to make sure that the first on has finished.

The more robust way is to use the scheduler to define a chain so that the jobs are linked. There's a fair few moving parts here, but its easy enough to put together.

- you bind your procedures to programs
- you bind your programs to steps in a chain
- you set rules for each step in the chain


SQL> create or replace procedure proc1 is begin null; end;
  2  /

Procedure created.

SQL>
SQL> create or replace procedure proc2 is begin null; end;
  2  /

Procedure created.

SQL>
SQL> begin
  2    dbms_scheduler.create_program (
  3      program_name   => 'PROG_1',
  4      program_type   => 'PLSQL_BLOCK',
  5      program_action => 'proc1;',
  6      enabled        => true);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> begin
  2    dbms_scheduler.create_program (
  3      program_name   => 'PROG_2',
  4      program_type   => 'PLSQL_BLOCK',
  5      program_action => 'proc2;',
  6      enabled        => true);
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> exec  dbms_scheduler.create_chain (chain_name=>'MY_CHAIN')

PL/SQL procedure successfully completed.

SQL> exec  dbms_scheduler.define_chain_step('MY_CHAIN','STEP1','PROG_1');

PL/SQL procedure successfully completed.

SQL> exec  dbms_scheduler.define_chain_step('MY_CHAIN','STEP2','PROG_2');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> exec  dbms_scheduler.define_chain_rule ('MY_CHAIN','TRUE','START "STEP1"','RULE_1');

PL/SQL procedure successfully completed.

SQL> exec  dbms_scheduler.define_chain_rule ('MY_CHAIN','"STEP1" COMPLETED','START "STEP2"','RULE_2');

PL/SQL procedure successfully completed.

SQL> exec  dbms_scheduler.define_chain_rule ('MY_CHAIN','"STEP2" COMPLETED','END','RULE_3');

PL/SQL procedure successfully completed.

SQL>
SQL> exec  dbms_scheduler.enable ('MY_CHAIN');

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> BEGIN
  2    dbms_scheduler.create_job (
  3      job_name=> 'MY_JOB',
  4      job_type=> 'CHAIN',
  5      job_action=> 'MY_CHAIN',
  6      start_date=> sysdate,
  7      enabled=> true);
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL>

See dbms_scheduler.define_chain_rule for details on what you can use in the 'condition' parameter to control day of week etc.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

question not answered

Herta, March 20, 2018 - 5:34 pm UTC

The original question was: "I want JOB2 to start on two conditions : JOB1 has finished and we are on monday."

It was not answered. The example given shows how to chain two programs, and then the reader is left with "See dbms_scheduler.define_chain_rule for details on what you can use in the 'condition' parameter to control day of week etc."

I have a similar issue: job 1 needs to run daily, job 2 needs to start after job 1 completes, but should only run on the first day of the month.

I spent several hours reading the dbms_scheduler.define_chain_rule documentation, and looking for practical examples, but am no further than when I started the quest.

A clear example would be welcome.

April 06, 2018 - 4:24 am UTC

@drop t

create table t ( x date, y int );

create or replace procedure proc1 is begin 
 insert into t values (sysdate,1);
 commit;
end;
/



create or replace procedure proc2 is begin
  insert into t values (sysdate,2);
  commit;
end;
/

 begin
   dbms_scheduler.create_program (
     program_name   => 'PROG_1',
     program_type   => 'PLSQL_BLOCK',
     program_action => 'proc1;',
     enabled        => true);
 end;
 /



 begin
   dbms_scheduler.create_program (
     program_name   => 'PROG_2',
     program_type   => 'PLSQL_BLOCK',
     program_action => 'proc2;',
     enabled        => true);
 end;
 /


 exec  dbms_scheduler.create_chain (chain_name=>'MY_CHAIN')
 exec  dbms_scheduler.define_chain_step('MY_CHAIN','STEP1','PROG_1');
 exec  dbms_scheduler.define_chain_step('MY_CHAIN','STEP2','PROG_2');
 exec  dbms_scheduler.define_chain_rule ('MY_CHAIN','TRUE','START "STEP1"','RULE_1');
 exec  dbms_scheduler.define_chain_rule ('MY_CHAIN','"STEP1" COMPLETED','START "STEP2"','RULE_2');
 exec  dbms_scheduler.define_chain_rule ('MY_CHAIN','"STEP2" COMPLETED','END','RULE_3');
 exec  dbms_scheduler.enable ('MY_CHAIN');

 BEGIN
   dbms_scheduler.create_job (
     job_name=> 'MY_JOB',
     job_type=> 'CHAIN',
     job_action=> 'MY_CHAIN',
     start_date=> sysdate,
     enabled=> true);
 END;
 /

exec  dbms_scheduler.drop_chain('MY_CHAIN',force=>true);

 exec  dbms_scheduler.create_chain (chain_name=>'MY_CHAIN')
 exec  dbms_scheduler.define_chain_step('MY_CHAIN','STEP1','PROG_1');
 exec  dbms_scheduler.define_chain_step('MY_CHAIN','STEP2','PROG_2');
 exec  dbms_scheduler.define_chain_rule ('MY_CHAIN','TRUE','START "STEP1"','RULE_1');
 exec  dbms_scheduler.define_chain_rule ('MY_CHAIN',':step1.state=''SUCCEEDED''  AND to_char(sysdate,''DY'') = ''FRI''','START "STEP2"','RULE_2');
 exec  dbms_scheduler.define_chain_rule ('MY_CHAIN','"STEP2" COMPLETED','END','RULE_3');
 exec  dbms_scheduler.enable ('MY_CHAIN');

 BEGIN
   dbms_scheduler.create_job (
     job_name=> 'MY_JOB',
     job_type=> 'CHAIN',
     job_action=> 'MY_CHAIN',
     start_date=> sysdate,
     enabled=> true);
 END;
 /


exec  dbms_scheduler.drop_chain('MY_CHAIN',force=>true);

 exec  dbms_scheduler.create_chain (chain_name=>'MY_CHAIN')
 exec  dbms_scheduler.define_chain_step('MY_CHAIN','STEP1','PROG_1');
 exec  dbms_scheduler.define_chain_step('MY_CHAIN','STEP2','PROG_2');
 exec  dbms_scheduler.define_chain_rule ('MY_CHAIN','TRUE','START "STEP1"','RULE_1');
 exec  dbms_scheduler.define_chain_rule ('MY_CHAIN',':step1.state=''SUCCEEDED''  AND to_char(sysdate,''DY'') = ''MON''','START "STEP2"','RULE_2');
 exec  dbms_scheduler.define_chain_rule ('MY_CHAIN','"STEP2" COMPLETED','END','RULE_3');
 exec  dbms_scheduler.enable ('MY_CHAIN');

BEGIN
   dbms_scheduler.create_job (
     job_name=> 'MY_JOB',
     job_type=> 'CHAIN',
     job_action=> 'MY_CHAIN',
     start_date=> sysdate,
     enabled=> true);
 END;
 /

Two schedules...

A reader, April 06, 2018 - 8:19 am UTC

Why not just create two schedules?
Schedule 1: Runs proc1; proc2; on Mondays at 00:30
Schedule 2: Runs proc1; on all other days at 00:30.

April 08, 2018 - 6:29 am UTC

Lots of different ways to do this.

eg

begin
  proc1;
  if to_char(sysdate,'DY') = 'MON' then
    proc2; 
  end if;
end;

etc etc

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

question not answered

Two schedules...

More to Explore

PL/SQL demos

PL/SQL docs

How to start a job on two conditions with DBMS_SCHEDULER : another job has finished and we are on monday ?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

question not answered

Two schedules...

More to Explore

PL/SQL demos

PL/SQL docs