Call (800) 766-1884 for Oracle support & training

Oracle Scheduler Example Job Chain

Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan). The following is an excerpt from the book.

The point has been reached where coding the specific example is desired. The job_chain_aq.sql script creates a package specification and body that will do all the work for the example job chain.

job_chain_aq.sql

CREATE OR REPLACE PACKAGE job_chain_aq AS

PROCEDURE task_1;

PROCEDURE task_2;

PROCEDURE task_3;

PROCEDURE enqueue_message (p_queue_name IN VARCHAR2);

PROCEDURE dequeue_message (p_queue_name IN VARCHAR2);

END job_chain_aq;

SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY job_chain_aq 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 signal task_2

enqueue_message (p_queue_name => 'task_2_queue');

EXCEPTION

WHEN OTHERS THEN

-- Don't signal task_2.

NULL;

END task_1;

-- -----------------------------------------------------------------

PROCEDURE task_2 AS

-- -----------------------------------------------------------------

BEGIN

dequeue_message (p_queue_name => 'task_2_queue');

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 signal task_3

enqueue_message (p_queue_name => 'task_3_queue');

EXCEPTION

WHEN OTHERS THEN

-- Don't signal task_3.

NULL;

END task_2;

-- -----------------------------------------------------------------

PROCEDURE task_3 AS

-- -----------------------------------------------------------------

BEGIN

dequeue_message (p_queue_name => 'task_3_queue');

INSERT INTO job_chain (created_timestamp, task_name)

VALUES (systimestamp, 'TASK_3');

COMMIT;

END task_3;

-- -----------------------------------------------------------------

PROCEDURE enqueue_message (p_queue_name IN VARCHAR2) AS

-- -----------------------------------------------------------------

l_enqueue_options DBMS_AQ.enqueue_options_t;

l_message_properties DBMS_AQ.message_properties_t;

l_message_handle RAW(16);

l_job_chain_msg job_chain_msg_type;

BEGIN

l_job_chain_msg := job_chain_msg_type('GO');

DBMS_AQ.enqueue(queue_name => 'job_user.' || p_queue_name,

enqueue_options => l_enqueue_options,

message_properties => l_message_properties,

payload => l_job_chain_msg,

msgid => l_message_handle);

END enqueue_message;

-- -----------------------------------------------------------------

PROCEDURE dequeue_message (p_queue_name IN VARCHAR2) AS

-- -----------------------------------------------------------------

l_dequeue_options DBMS_AQ.dequeue_options_t;

l_message_properties DBMS_AQ.message_properties_t;

l_message_handle RAW(16);

l_job_chain_msg job_chain_msg_type;

BEGIN

DBMS_AQ.dequeue(queue_name => 'job_user.' || p_queue_name,

dequeue_options => l_dequeue_options,

message_properties => l_message_properties,

payload => l_job_chain_msg,

msgid => l_message_handle);

END dequeue_message;

-- -----------------------------------------------------------------

END job_chain_aq;

SHOW ERRORS

Next, the jobs associated with each task are scheduled. Unlike the previous example, the job sequence is protected by the queue, so all the jobs can be enabled.

job_chain_aq_jobs.sql

-- Oracle

BEGIN

DBMS_SCHEDULER.create_job (

job_name => 'job_chain_aq_task_1',

job_type => 'STORED_PROCEDURE',

job_action => 'job_chain_aq.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_aq_task_2',

job_type => 'STORED_PROCEDURE',

job_action => 'job_chain_aq.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_aq_task_3',

job_type => 'STORED_PROCEDURE',

job_action => 'job_chain_aq.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;

EXEC DBMS_SCHEDULER.run_job ('job_chain_aq_task_1');

-- Oracle

BEGIN

DBMS_SCHEDULER.drop_job ('job_chain_aq_task_3');

DBMS_SCHEDULER.drop_job ('job_chain_aq_task_2');

DBMS_SCHEDULER.drop_job ('job_chain_aq_task_1');

END;

At this point, the tasks are scheduled but have not been executed; therefore, there are no results in the job_chain table or the job_chain_queue_tab table. Rather than waiting until 6:00, the first job is forced to run immediately. The results below show that the first task has run and there is a message waiting in the queue table on the task_2_queue.

SQL> exec dbms_scheduler.run_job ('job_chain_aq_task_1');

PL/SQL procedure successfully completed.

job_user@db10g> @job_chain_query.sql

CREATED_TIMESTAMP TASK_NAME

--------------------------- --------------------

07-AUG-2004 18:18:36.136000 TASK_1

SQL> @job_chain_aq_query.sql

QUEUE MESSAGES

------------------------------ ----------

TASK_2_QUEUE 1

If the run of the second job is forced, the second task reads a message from its queue, completes its processing and places a message on the queue for the third task.

SQL> exec dbms_scheduler.run_job ('job_chain_aq_task_2');

PL/SQL procedure successfully completed.

SQL> @job_chain_query.sql

CREATED_TIMESTAMP TASK_NAME

--------------------------- --------------------

07-AUG-2004 18:18:36.136000 TASK_1

07-AUG-2004 18:23:08.771000 TASK_2

SQL> @job_chain_aq_query.sql

QUEUE MESSAGES

------------------------------ ----------

TASK_3_QUEUE 1

If the run of the third job is forced, the third task reads a message from its queue and completes its processing.