Conditional Job Runs using a Custom Table
Solution
If none of the previous methods seem suitable,
a specific solution to meet specific needs can always be built..
The following example could be used as a starting point for such a
solution.
The sequence of jobs is protected using the
job_chain_locks table. The RETRIES column specifies the number
of times a task should check the locks before it gives up and
re-schedules itself. The RETRY_DELAY column specifies the
number of minutes between retries. The TASK_NAME and LOCKED
columns are self-explanatory.
*
job_chain_locks.sql
CREATE TABLE
job_chain_locks (
task_name VARCHAR2(20)
NOT NULL,
locked VARCHAR2(1)
DEFAULT 'Y' NOT NULL,
retries NUMBER(3)
DEFAULT 0 NOT NULL,
retry_delay NUMBER(3) DEFAULT 1
NOT NULL,
CONSTRAINT job_chain_locks_pk PRIMARY KEY (task_name)
);
INSERT INTO
job_chain_locks (task_name, locked, retries, retry_delay)
VALUES ('task_2', 'Y', 5, 1);
INSERT INTO
job_chain_locks (task_name, locked, retries, retry_delay)
VALUES ('task_3', 'Y', 3, 1);
COMMIT;
The contents
of the job_chain_locks table can be monitored using the
job_chain_locks_query.sql script.
*
job_chain_locks_query.sql
--
*************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select
*
from
job_chain_locks
order by
task_name
;
The
job_chain_custom.sql script creates a package specification and body
which will do all the work for the example job chain.
*
job_chain_custom_sql
--
*************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
CREATE OR
REPLACE PACKAGE job_chain_custom AS
PROCEDURE
task_1;
PROCEDURE task_2;
PROCEDURE task_3;
PROCEDURE lock_task (p_task_name IN
job_chain_locks.task_name%TYPE,
p_lock IN BOOLEAN DEFAULT
TRUE);
FUNCTION unlocked (p_task_name IN
job_chain_locks.task_name%TYPE)
RETURN BOOLEAN;
END
job_chain_custom;
/
SHOW ERRORS
CREATE OR
REPLACE PACKAGE BODY job_chain_custom AS
--
-----------------------------------------------------------------
PROCEDURE task_1 AS
-- -----------------------------------------------------------------
BEGIN
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 unlock task_2
lock_task ('task_2', FALSE);
EXCEPTION
WHEN OTHERS THEN
-- Don't unlock task_2.
NULL;
END task_1;
-- -----------------------------------------------------------------
--
-----------------------------------------------------------------
PROCEDURE task_2 AS
-- -----------------------------------------------------------------
BEGIN
IF
unlocked('task_2') THEN
lock_task ('task_2');
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 unlock task_3
lock_task ('task_3', FALSE);
END IF;
EXCEPTION
WHEN OTHERS THEN
-- Don't unlock task_3.
NULL;
END task_2;
-- -----------------------------------------------------------------
--
-----------------------------------------------------------------
PROCEDURE task_3 AS
-- -----------------------------------------------------------------
BEGIN
IF
unlocked('task_3') THEN
lock_task ('task_3');
INSERT INTO job_chain (created_timestamp, task_name)
VALUES (systimestamp, 'TASK_3');
COMMIT;
END IF;
END task_3;
-- -----------------------------------------------------------------
--
-----------------------------------------------------------------
PROCEDURE lock_task (p_task_name IN
job_chain_locks.task_name%TYPE,
p_lock IN BOOLEAN DEFAULT
TRUE) AS
-- -----------------------------------------------------------------
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE job_chain_locks
SET locked = 'Y'
WHERE task_name = p_task_name;
COMMIT;
END lock_task;
-- -----------------------------------------------------------------
--
-----------------------------------------------------------------
FUNCTION unlocked (p_task_name IN
job_chain_locks.task_name%TYPE)
RETURN BOOLEAN AS
-- -----------------------------------------------------------------
l_jcl_row job_chain_locks%ROWTYPE;
BEGIN
SELECT *
INTO l_jcl_row
FROM job_chain_locks
WHERE task_name = p_task_name;
IF
l_jcl_row.locked != 'Y' THEN
RETURN TRUE;
END IF;
FOR i
IN 1 .. l_jcl_row.retries LOOP
DBMS_LOCK.sleep(60 * l_jcl_row.retry_delay);
SELECT locked
INTO l_jcl_row.locked
FROM job_chain_locks
WHERE task_name = p_task_name;
IF l_jcl_row.locked != 'Y' THEN
RETURN TRUE;
END IF;
END LOOP;
RETURN
FALSE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END unlocked;
-- -----------------------------------------------------------------
END
job_chain_custom;
/
SHOW ERRORS
Next, the jobs associated with each task are
scheduled.
*
job_chain_custom_jobs.sql
--
*************************************************
-- Copyright ? 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
-- Oracle10g
BEGIN
DBMS_SCHEDULER.create_job (
job_name =>
'job_chain_custom_task_1',
job_type => 'STORED_PROCEDURE',
job_action =>
'job_chain_custom.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 AQ chain.');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name =>
'job_chain_custom_task_2',
job_type => 'STORED_PROCEDURE',
job_action =>
'job_chain_custom.task_2',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=12; byminute=0;
bysecond=0;',
end_date => NULL,
enabled =>
TRUE,
comments => 'Second
task in the AQ chain.');
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name =>
'job_chain_custom_task_3',
job_type => 'STORED_PROCEDURE',
job_action =>
'job_chain_custom.task_3',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=18; byminute=0;
bysecond=0;',
end_date => NULL,
enabled =>
TRUE,
comments => 'Third
task in the AQ chain.');
END;
/
-- Pre
Oracle10g
/*
BEGIN
DBMS_JOB.isubmit (
job => 1000,
what => 'BEGIN
job_chain_custom.task_1; END;',
next_date => SYSDATE,
interval => 'TRUNC(SYSDATE) + INTERVAL ''1 6'' DAY TO
HOUR');
DBMS_JOB.isubmit (
job => 1001,
what => 'BEGIN
job_chain_custom.task_2; END;',
next_date => SYSDATE,
interval => 'TRUNC(SYSDATE) + INTERVAL ''1 12'' DAY TO
HOUR');
DBMS_JOB.isubmit (
job => 1002,
what => 'BEGIN
job_chain_custom.task_3; END;',
next_date => SYSDATE,
interval => 'TRUNC(SYSDATE) + INTERVAL ''1 18'' DAY TO
HOUR');
COMMIT;
END;
/
*/
-- Cleanup
/*
-- Oracle10g
BEGIN
DBMS_SCHEDULER.drop_job ('job_chain_custom_task_3');
DBMS_SCHEDULER.drop_job ('job_chain_custom_task_2');
DBMS_SCHEDULER.drop_job ('job_chain_custom_task_1');
END;
/
-- Pre
Oracle10g
BEGIN
DBMS_JOB.remove(1002);
DBMS_JOB.remove(1001);
DBMS_JOB.remove(1000);
COMMIT;
END;
/
*/
At this point, the tasks are scheduled but have
not been executed; hence, no results in the job_chain table.
Rather than waiting until 6:00, the first job can be forced to run
immediately. The results below show that the first task has
run and second task has been unlocked.
SQL> exec
dbms_scheduler.run_job ('job_chain_custom_task_1');
PL/SQL
procedure successfully completed.
SQL> @job_chain_query.sql
CREATED_TIMESTAMP
TASK_NAME
--------------------------- --------------------
07-AUG-2004 19:54:51.010000 TASK_1
1 row
selected.
SQL> @job_chain_locks_query.sql
TASK_NAME
L RETRIES RETRY_DELAY
-------------------- - ---------- -----------
task_2
N 5
1
task_3
Y 3
1
2 rows
selected.
Running the second job manually results in the
second task being relocked and the third task being unlocked.
SQL> exec
dbms_scheduler.run_job ('job_chain_custom_task_2');
PL/SQL
procedure successfully completed.
SQL> @job_chain_query.sql
CREATED_TIMESTAMP
TASK_NAME
--------------------------- --------------------
07-AUG-2004 19:54:51.010000 TASK_1
07-AUG-2004 19:57:29.636000 TASK_2
2 rows
selected.
SQL> @job_chain_locks_query.sql
TASK_NAME
L RETRIES RETRY_DELAY
-------------------- - ---------- -----------
task_2
Y 5
1
task_3 N
3 1
2 rows
selected.
Running the third job manually results in the
second task being relocked.
SQL> exec
dbms_scheduler.run_job ('job_chain_custom_task_3');
PL/SQL
procedure successfully completed.
SQL> @job_chain_query.sql
CREATED_TIMESTAMP
TASK_NAME
--------------------------- --------------------
07-AUG-2004 19:54:51.010000 TASK_1
07-AUG-2004 19:57:29.636000 TASK_2
07-AUG-2004 19:59:11.184000 TASK_3
3 rows
selected.
SQL> @job_chain_locks_query.sql
TASK_NAME
L RETRIES RETRY_DELAY
-------------------- - ---------- -----------
task_2
Y 5
1
task_3
Y 3
1
2 rows
selected.
Attempting to run a job out of order will
result in the session hanging until the task is unlocked or the
appropriate number of retries has been attempted; at which point the
job is rescheduled.
SQL> set
timing on
SQL> exec
dbms_scheduler.run_job ('job_chain_custom_task_3');
PL/SQL
procedure successfully completed.
Elapsed:
00:03:04.50
The DBA now has a variety of tools available
with which to build job chains. The next section will
introduce the error handling requirements associated with job
scheduling.
 |
This is an excerpt from the book "Oracle
Job Scheduling" by Dr. Tim Hall. You can buy it direct
from the publisher for 30%-off and get instant access to the
code depot of Oracle job scheduling scripts. |
and include the URL for the page.
