Batch work often comes in steps: load data, check it, then report. A Scheduler chain runs such steps as one job, with rules that decide which step starts when another succeeds or fails. The whole flow is defined in the database, logged step by step, and started like any other job.
Code for This Guide
The main examples are in the examples/pkg-scheduler folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_scheduler.create_chain(chain_name); dbms_scheduler.define_chain_step(chain_name, step_name, program_name); dbms_scheduler.define_chain_rule(chain_name, condition, action); dbms_scheduler.enable(chain_name); dbms_scheduler.create_job(job_name, job_type => 'CHAIN', job_action => chain_name);
Conditions such as 'TRUE', 'STEP_A SUCCEEDED', 'STEP_A FAILED', or 'STEP_A COMPLETED' combine with AND and OR. Actions are 'START step', or 'END' to finish the chain.
The first example logs to a small table:
Setup:
create table job_log (logged_at timestamp default systimestamp,
job_name varchar2(30), message varchar2(200));Load, Check, Report
Example:
begin
dbms_scheduler.create_program('LOAD_FUEL', 'PLSQL_BLOCK',
q'[begin insert into job_log (job_name, message) values ('CHAIN', 'load fuel prices');
end;]', enabled => true);
dbms_scheduler.create_program('CHECK_FUEL', 'PLSQL_BLOCK',
q'[begin insert into job_log (job_name, message) values ('CHAIN', 'check fuel prices');
end;]', enabled => true);
dbms_scheduler.create_program('REPORT_FUEL', 'PLSQL_BLOCK',
q'[begin insert into job_log (job_name, message) values ('CHAIN', 'report fuel prices');
end;]', enabled => true);
dbms_scheduler.create_chain('FUEL_CHAIN');
dbms_scheduler.define_chain_step('FUEL_CHAIN', 'LOAD_STEP', 'LOAD_FUEL');
dbms_scheduler.define_chain_step('FUEL_CHAIN', 'CHECK_STEP', 'CHECK_FUEL');
dbms_scheduler.define_chain_step('FUEL_CHAIN', 'REPORT_STEP', 'REPORT_FUEL');
dbms_scheduler.define_chain_rule('FUEL_CHAIN', 'TRUE', 'START LOAD_STEP');
dbms_scheduler.define_chain_rule('FUEL_CHAIN', 'LOAD_STEP SUCCEEDED', 'START CHECK_STEP');
dbms_scheduler.define_chain_rule('FUEL_CHAIN', 'CHECK_STEP SUCCEEDED',
'START REPORT_STEP');
dbms_scheduler.define_chain_rule('FUEL_CHAIN', 'REPORT_STEP COMPLETED', 'END');
dbms_scheduler.enable('FUEL_CHAIN');
dbms_scheduler.create_job('FUEL_JOB', job_type => 'CHAIN', job_action => 'FUEL_CHAIN',
enabled => true);
end;
/
exec dbms_session.sleep(8)
select message from job_log where job_name = 'CHAIN' order by logged_at;
select job_subname as step, status, error#
from user_scheduler_job_run_details
where job_name = 'FUEL_JOB' and job_subname is not null
and log_date > systimestamp - interval '1' minute
order by log_id;Output:
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. MESSAGE _____________________ load fuel prices check fuel prices report fuel prices STEP STATUS ERROR# ______________ ____________ _________ LOAD_STEP SUCCEEDED 0 CHECK_STEP SUCCEEDED 0 REPORT_STEP SUCCEEDED 0
Three programs become three steps. The rules start the load first, the check after the load succeeds, and the report after the check succeeds, then end the chain. JOB_LOG shows the steps in order, and the run details list each step by its name as JOB_SUBNAME.
Branch on Failure
Rules can also react to a failed step. Here the check always fails, so the chain sends an alert instead of publishing.
Example:
begin
dbms_scheduler.create_program('X_CHECK', 'PLSQL_BLOCK',
q'[begin raise_application_error(-20003, 'prices out of range'); end;]',
enabled => true);
dbms_scheduler.create_program('X_PUBLISH', 'PLSQL_BLOCK',
q'[begin insert into chain_log (message) values ('published'); end;]', enabled => true);
dbms_scheduler.create_program('X_ALERT', 'PLSQL_BLOCK',
q'[begin insert into chain_log (message) values ('alert sent'); end;]',
enabled => true);
dbms_scheduler.create_chain('CHECK_CHAIN');
dbms_scheduler.define_chain_step('CHECK_CHAIN', 'CHECK_STEP', 'X_CHECK');
dbms_scheduler.define_chain_step('CHECK_CHAIN', 'PUBLISH_STEP', 'X_PUBLISH');
dbms_scheduler.define_chain_step('CHECK_CHAIN', 'ALERT_STEP', 'X_ALERT');
dbms_scheduler.define_chain_rule('CHECK_CHAIN', 'TRUE', 'START CHECK_STEP');
dbms_scheduler.define_chain_rule('CHECK_CHAIN', 'CHECK_STEP SUCCEEDED',
'START PUBLISH_STEP');
dbms_scheduler.define_chain_rule('CHECK_CHAIN', 'CHECK_STEP FAILED', 'START ALERT_STEP');
dbms_scheduler.define_chain_rule('CHECK_CHAIN',
'PUBLISH_STEP COMPLETED OR ALERT_STEP COMPLETED', 'END');
dbms_scheduler.enable('CHECK_CHAIN');
dbms_scheduler.create_job('CHECK_JOB', job_type => 'CHAIN', job_action => 'CHECK_CHAIN',
enabled => true);
end;
/
exec dbms_session.sleep(8)
select message from chain_log;
select job_subname as step, status, error#
from user_scheduler_job_run_details
where job_name = 'CHECK_JOB' and job_subname is not null
and log_date > systimestamp - interval '15' second -- this run
order by log_id;Output:
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. MESSAGE _____________ alert sent STEP STATUS ERROR# _____________ ____________ _________ CHECK_STEP FAILED 20003 ALERT_STEP SUCCEEDED 0
CHECK_STEP failed with error 20003, the CHECK_STEP FAILED rule started ALERT_STEP, and PUBLISH_STEP never ran. The example drops the job, chain, programs, and table afterward.
Things to Know
- Every chain needs a rule that ends it, or the chain job stays running after its last step.
- Avoid reserved words such as CHECK as step names; rule conditions fail to compile with them.
- USER_SCHEDULER_RUNNING_CHAINS shows the state of each step of a chain that is running.
Cleanup for the first example:
exec dbms_scheduler.drop_job('FUEL_JOB', force => true)
exec dbms_scheduler.drop_chain('FUEL_CHAIN', force => true)
exec dbms_scheduler.drop_program('LOAD_FUEL, CHECK_FUEL, REPORT_FUEL')
drop table job_log purge;Related Guides
- How to Create Scheduler Programs in Oracle (schedules, arguments)
- How to Handle Scheduler Job Failures in Oracle (job log, retries)
Conclusion
Scheduler chains run multi-step batch work as one job, with rules that start steps on success or failure. Define programs, steps, and rules, always include an END rule, and follow each step in the job run details.
