In this method, all tasks in the chain are
scheduled as regular repeating jobs, but only
the first job in the chain is enabled.
All subsequent jobs in the chain are
disabled or marked as broken prior to Oracle.
As each task in the chain completes
successfully, it enables the next task in the
chain by enabling its associated job.
Every time the first task runs, it
disables the chain before starting again.
The
job_chain_enable.sql
script creates a package specification and body
that will do all the work for the example job
chain.
CREATE OR REPLACE PACKAGE
job_chain_enable AS
PROCEDURE task_1;
PROCEDURE task_2;
PROCEDURE task_3;
END job_chain_enable;
/
SHOW ERRORS
CREATE OR REPLACE PACKAGE
BODY job_chain_enable AS
--
-----------------------------------------------------------------
PROCEDURE task_1 AS
--
-----------------------------------------------------------------
BEGIN
-- Disable dependent jobs
-- Oracle
DBMS_SCHEDULER.disable
('job_chain_enable_task_2');
DBMS_SCHEDULER.disable
('job_chain_enable_task_3');
DELETE FROM job_chain;
INSERT INTO job_chain (created_timestamp,
task_name)
VALUES (systimestamp, 'TASK_1');
COMMIT;
-- Uncomment the following line to force a
failure.
--RAISE_APPLICATION_ERROR(-20000,
--
'This is a fake error to prevent
task_2 being executed');
-- The work has comleted successfully so
enable task_2
-- Oracle
DBMS_SCHEDULER.enable
('job_chain_enable_task_2');
EXCEPTION
WHEN OTHERS THEN
-- Don't enable task_2.
NULL;
END task_1;
--
-----------------------------------------------------------------
--
-----------------------------------------------------------------
PROCEDURE task_2 AS
--
-----------------------------------------------------------------
BEGIN
INSERT INTO job_chain (created_timestamp,
task_name)
VALUES (systimestamp, 'TASK_2');
COMMIT;
-- Uncomment the following line to force a
failure.
--RAISE_APPLICATION_ERROR(-20000,
--
'This is a fake error to prevent
task_3 being executed');
-- The work has comleted successfully so
enable task_3
-- Oracle
DBMS_SCHEDULER.enable
('job_chain_enable_task_3');
EXCEPTION
WHEN OTHERS THEN
-- Don't enable task_3.
NULL;
END task_2;
--
-----------------------------------------------------------------
--
-----------------------------------------------------------------
PROCEDURE task_3 AS
--
-----------------------------------------------------------------
BEGIN
INSERT INTO job_chain (created_timestamp,
task_name)
VALUES (systimestamp, 'TASK_3');
COMMIT;
END task_3;
--
-----------------------------------------------------------------
END job_chain_enable;
/
SHOW ERRORS
Since no jobs are created by the code, they must
all be created in advance using the
job_chain_enable_jobs.sql
script.
The jobs must persist, so they are
generated with repeat intervals.
These
repeat intervals schedule them to run at 06:00,
12:00 and 18:00 respectively.
Commands to remove the jobs are included
and should be run once the example is completed.
job_chain_enable_jobs.sql
-- Oracle
BEGIN
DBMS_SCHEDULER.create_job (
job_name
=> 'job_chain_enable_task_1',
job_type
=> 'STORED_PROCEDURE',
job_action
=> 'job_chain_enable.task_1',
start_date
=> SYSTIMESTAMP,
repeat_interval => 'freq=daily;
byhour=6; byminute=0; bysecond=0;',
end_date
=> NULL,
enabled
=> TRUE,
comments
=> 'First task in the enable
chain.');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name
=> 'job_chain_enable_task_2',
job_type
=> 'STORED_PROCEDURE',
job_action
=> 'job_chain_enable.task_2',
start_date
=> SYSTIMESTAMP,
repeat_interval => 'freq=daily;
byhour=12; byminute=0; bysecond=0;',
end_date
=> NULL,
enabled
=> FALSE,
comments
=> 'Second task in the enable
chain.');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name
=> 'job_chain_enable_task_3',
job_type
=> 'STORED_PROCEDURE',
job_action
=> 'job_chain_enable.task_3',
start_date
=> SYSTIMESTAMP,
repeat_interval => 'freq=daily;
byhour=18; byminute=0; bysecond=0;',
end_date
=> NULL,
enabled
=> FALSE,
comments
=> 'Third task in the enable
chain.');
END;
/
-- Cleanup
/*
-- Oracle
BEGIN
DBMS_SCHEDULER.drop_job
('job_chain_enable_task_3');
DBMS_SCHEDULER.drop_job
('job_chain_enable_task_2');
DBMS_SCHEDULER.drop_job
('job_chain_enable_task_1');
END;
/
