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 reschedules itself.
The RETRY_DELAY column specifies the
number of minutes between retries.
The TASK_NAME and LOCKED columns are
self-explanatory.
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
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.
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
-- Oracle
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;
/
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
SQL> @job_chain_locks_query.sql
TASK_NAME
L
RETRIES RETRY_DELAY
-------------------- -
---------- -----------
task_2
N
5
1
task_3
Y
3
1
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
SQL> @job_chain_locks_query.sql
TASK_NAME
L
RETRIES RETRY_DELAY
-------------------- -
---------- -----------
task_2
Y
5
1
task_3
N
3
1
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.
