To implement a dependency between jobs, you can chain them together. Chains allow you to define what action is to be taken based on the outcome of a job or more jobs. In other words, you can define a logic (from very simple to very complex) similar to IF-THEN-ELSE or CASE-WHEN-ELSE if you will, where you define what is the order of jobs that are chained to together.
I will now show you basic of chain creation, but beware – the more complex the structure is the more complicated it might be for you to orient yourself there. Keep it simple and do not make it too complicated.
First, you have to create a chain object (you do not set RULE_SET_NAME and EVALUATION_INTERVAL … leave it NULL by default .. if you are interested in details for these two, check the Oracle’s documentation pages, please. However, it might get tricky.)
BEGIN DBMS_SCHEDULER.CREATE_CHAIN ( chain_name => 'my_test_chain', -- this will be used in the chain step creation rule_set_name => NULL, evaluation_interval => NULL, comments => 'This is my comment'); END; / -- to schedule it, just use the keyword CHAIN from dbms_scheduler.create_job BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'MY_CHAIN_JOB', job_type => 'CHAIN', job_action => 'my_test_chain', -- here comes your chain name repeat_interval => 'freq=daily;interval=1', enabled => TRUE); END; /
After that, in the next step, you have to define chain steps … you can either refer to a scheduler or to another chain (to create a nested chain)…
BEGIN DBMS_SCHEDULER.DEFINE_CHAIN_STEP ( chain_name => 'my_test_chain', -- this must be the same name you used in the chain object definition / creation step_name => 'prepare_input_data', program_name => 'BEGIN my_schema.prcd_prepare_data(); END;'); DBMS_SCHEDULER.DEFINE_CHAIN_STEP ( chain_name => 'my_test_chain', -- this must be the same name you used in the chain object definition / creation step_name => 'process_prepared_data', program_name => 'BEGIN my_schema.prcd_process_data(); END;'); -- eventually, you can call another chain DBMS_SCHEDULER.DEFINE_CHAIN_STEP ( chain_name => 'my_test_chain', -- this must be the same name you used in the chain object definition / creation step_name => 'final_aggregation', program_name => 'my_another_chain'); -- this might be the name of the chain you would like to nest END;'); END; /
And now, you have to define rules for chaining and the actual dependency
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'my_test_chain', -- this must be the same name you used in the chain object definition / creation
condition => 'TRUE', -- always true as this is a first step
action => 'START prepare_input_data', -- this is the name of the step from the previous CHAIN_STEP definition …
-- it does not need to correspond the order of creation .. just use the step_name you would like to run first
comments => 'Preparation starts ...');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'my_test_chain', -- this must be the same name you used in the chain object definition / creation
condition => 'prepare_input_data SUCCEEDED', -- if the first step succeeds do the following action
action => 'START process_prepared_data',
comments => 'Processing prepared starts ...');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'my_test_chain', -- this must be the same name you used in the chain object definition / creation
condition => 'prepare_input_data SUCCEEDED AND process_input_data NOT SUCCEEDED', -- if the first step is ok but the second did not succeed !!!
action => 'END',
comments => 'Failure; second step failed');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'my_test_chain', -- this must be the same name you used in the chain object definition / creation
condition => 'prepare_input_data SUCCEEDED AND process_input_data COMPLETED', -- if the first is succeeded and the second one is completed run the final aggregation
action => 'START final_aggregation',
comments => 'Final aggregation starts ...');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'my_test_chain', -- this must be the same name you used in the chain object definition / creation
condition => 'final_aggregation SUCCEEDED OR final_aggregation FAILED or final_aggregation STOPPED', -- if the final_aggregation step (consider the last one in this example) ends (with any status) ... call END
action => 'END',
comments => 'Finish');
END;
/
There must always be at least one action => ‘END’. The chain job will not complete until at least one condition/rule containing the END is evaluated to TRUE. Commonly, you have more than one ending (different rules). If your chain_rules will end up in a dead-end, the chain will be tagged as STALLED (which practically means “on-hold & unfinished”) and will be in this state until you fix it. For that reason, you have to make sure it won’t happen.
Picture it as an activity diagram where a result of each step must be handled properly. You can keep it very simple. Having said that does not mean you have to list down every status and set an action for that results. You can work smart and just make sure if it won’t do what you don’t want, the rest means END by using a logical NOT.
IF (step1 = 'SUCCEEDED') THEN run step2 IF (step1 NOT 'SUCCEEDED') THEN END /* if not succeeded, quit */ IF (step2 = 'SUCCEEDED') THEN run step3 etc..
But of course, you can implement a more complex logic as well
IF (step1 = 'SUCCEEDED') THEN run step2 IF (step1 = 'FAILED') THEN END /* or you can call another step .. just avoid calling the same step … you can fall into an infinite loop/deadlock */ IF (step2 = 'STOPPED') THEN run step3 IF (step1 = 'SUCCEEDED') AND (step2 = 'FAILED') AND (step3 = 'SUCCEEDED') THEN END IF (step3 = 'FAILED') AND (step2 = 'STOPPED') THEN END etc …
Check the diagram below representing the “logic” of chains 🙂
As I said, this was just a brief introduction because a book could be written about chaining. Refer to the documentation if you are serious about chains.