Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle Scheduling Creating a Job Chain

Oracle Tips by Burleson Consulting

Creating a Job Chain

Jobs are often defined as individual tasks that are performed in isolation; however, in some circumstances, a job consists of several tasks that must be performed as a whole in a specific sequence.  Typically, this would be accomplished by combining the tasks into a single job like the one defined below.

DBMS_SCHEDULER.create_job (
  job_name        => 'single_job',
  job_type        => 'PLSQL_BLOCK',
  job_action      => ?BEGIN
                        task1;
                        task2;
                        task3;
                      END;?,
  start_date      => SYSTIMESTAMP,
  repeat_interval => ?freq=daily; byhour=9; byminute=0; bysecond=0;?,
  end_date        => NULL,
  enabled         => TRUE,
  comments        => 'Single job.');

The problem arises when not all tasks can be performed at the same time.  For example, a batch of orders might process at midnight and produce the necessary billing paperwork at 9:00 a.m.  If no dependencies are defined between these tasks, any delays in the order processing may result in the generation of the billing paperwork before the orders are complete.

In these circumstances, a job chain needs to be created such that each task in the chain is performed in sequence and the failure of a single task breaks the chain.  This can be achieved in many ways, but the following methods are preferred:

* Conditional job creation.

* Conditional job enabling.

* Conditional job runs using Oracle Advanced Queuing.

* Conditional job runs using a custom table solution.

Most of the examples in this chapter will use the Oracle10g dbms_scheduler package, but there are earlier versions of Oracle in which the dbms_job package can be used effectively in its place.

Conditional Job Creation

In this method, the first task in the chain is scheduled as a regular repeating job, but all subsequent tasks are not scheduled. Instead, as each task in the chain completes successfully, it schedules the next task as a one-off job.

In the order and billing example, the time between tasks was long and the run times were fixed, excluding delays.  An example like this would not be very useful here since it would require a significant amount of time for the chain to complete successfully.  Instead, assume that a process made up of three tasks must run in sequence.  For the purposes of testing, the times between tasks should be relatively short and instead of fixed times, rolling times should be used.

In this example, each task will simply insert a record into a table, which can be created using the following script:

* job_chain_table.sql

CREATE TABLE job_chain (
  created_timestamp  TIMESTAMP,
  task_name          VARCHAR2(20)
);

The job_chain_create.sql script creates a package specification and body that will do all the work for the example job chain.

* job_chain_create.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_create AS

PROCEDURE task_1;
PROCEDURE task_2;
PROCEDURE task_3;

END job_chain_create;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY job_chain_create 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 create task_2
  -- Oracle10g
  DBMS_SCHEDULER.create_job (
    job_name        => 'job_chain_create_task_2',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'job_chain_create.task_2',
    start_date      => SYSTIMESTAMP + INTERVAL '2' MINUTE,
    repeat_interval => NULL,
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Second task in the create chain.');

  -- Pre Oracle10g
  /*
  DBMS_JOB.isubmit (
    job       => 1001,
    what      => 'BEGIN job_chain_create.task_2; END;' ,
    next_date => SYSDATE + INTERVAL '2' MINUTE); 

  COMMIT;
  */

EXCEPTION
  WHEN OTHERS THEN
    -- Don't create 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 create task_3
  -- Oracle10g
  DBMS_SCHEDULER.create_job (
    job_name        => 'job_chain_create_task_3',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'job_chain_create.task_3',
    start_date      => SYSTIMESTAMP + INTERVAL '2' MINUTE,
    repeat_interval => NULL,
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Third task in the create chain.');

  -- Pre Oracle10g
  /*
  DBMS_JOB.isubmit (
    job       => 1002,
    what      => 'BEGIN job_chain_create.task_3; END;' ,
    next_date => SYSDATE + INTERVAL '2' MINUTE);

  COMMIT;
  */

EXCEPTION
  WHEN OTHERS THEN
    -- Don't create 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_create;
/
SHOW ERRORS

Both task_1 and task_2 schedule a one-off job on successful completion.  Any exceptions are caught by the exception handler, which does not schedule the next job in the chain.

With the table and code in place, a job to call the first task using the job_chain_create_job.sql script can be scheduled.

* job_chain_create_job.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_create_task_1',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'job_chain_create.task_1',
    start_date      => SYSTIMESTAMP,
    repeat_interval => NULL,
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'First task in the create chain.');
END;
/

-- Pre Oracle10g
/*
BEGIN
  DBMS_JOB.isubmit (
    job       => 1000,
    what      => 'BEGIN job_chain_create.task_1; END;',
    next_date => SYSDATE);
  COMMIT;
END;
/
*/

The repeat_interval (or interval) parameter of this job definition is set to NULL making it a one-off job.  Under normal circumstances, this job is expected to be scheduled with a repeat interval since it is the first task in the chain. However, for the purposes of this example, the less clutter on the system the better, so no unnecessary repeating jobs are scheduled.

The progress of the job can be monitored using the following query:

* job_chain_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
-- *************************************************

alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS.FF';

set linesize 100
column created_timestamp format a27
column task_name format a20

select
   *
from
   job_chain
order by
   created_timestamp
;

On completion of the chain, the following output from the query is expected:

SQL> @job_chain_query.sql

CREATED_TIMESTAMP           TASK_NAME
--------------------------- --------------------
07-AUG-2004 10:49:42.701000 TASK_1
07-AUG-2004 10:51:42.858000 TASK_2
07-AUG-2004 10:53:43.093000 TASK_3

3 rows selected.

The result of breaks in the chain can be tested by uncommenting the lines in the code containing the raise_application_error procedure calls.  Uncommenting this line in task_1 would cause the chain to break during task_1 resulting in the following query output:

SQL> @job_chain_query.sql

CREATED_TIMESTAMP           TASK_NAME
--------------------------- --------------------
07-AUG-2004 11:03:11.827000 TASK_1

1 row selected.

Commenting out the statement in task_1 and uncommenting it in task_2 would cause the chain to break in task_2 resulting in the following query output:

SQL> job_chain_query.sql

CREATED_TIMESTAMP           TASK_NAME
--------------------------- --------------------
07-AUG-2004 11:10:42.746000 TASK_1
07-AUG-2004 11:12:42.956000 TASK_2

2 rows selected.

 

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.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational