Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle Scheduler Job Chain
Oracle Tips by Burleson Consulting

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

Jobs are often defined as individual tasks that are performed in isolation.  Yet 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

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

 

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

  -- Oracle

  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.');

 

 

SHOW ERRORS

 

Both task_1 and task_2 schedule a one-off job once successfully completed.  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

 

-- Oracle

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;

/

 

*/

 

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

 

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

 

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

 

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


Fo
r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.