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 


 

 

 


 

 

 
 

Chain together Oracle jobs

Oracle Database Tips by Donald BurlesonApril 29, 2013

Question:  How do I chain together job in the database? Can you provide a example script showing an example Oracle job chain?

Answer:  A Oracle job chain is a named series of programs that are linked together for a combined objective. Each position within a chain of interdependent programs is referred to as a step. Each step can point to one of the following: a program, another chain (a nested chain), an event.

Note: This feature introduced in Oracle 10g release 2.

To create and use a chain:

1. Create a chain object

DBMS_SCHEDULER.CREATE_CHAIN (
CHAIN_NAME => 'bulk_load_chain',
RULE_SET_NAME => NULL,
EVALUATION_INTERVAL => NULL,
COMMENTS => 'Load data and run reports')

2. Define one or more chain steps. You define a step that points to a program or nested chain.

DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
CHAIN_NAME => 'bulk_load_chain',
STEP_NAME => 'do_bulk_load',
PROGRAM_NAME => 'hr.load_data_prog)

Also you can define a step that waits for an event to occur by using the DEFINE_CHAIN_EVENT_STEP procedure. Procedure arguments can point to an event schedule or can include an in-line queue specification and event condition.

DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
CHAIN_NAME => 'bulk_load_chain',
STEP_NAME => 'stop_when_disk_full_evt'
EVENT_SCHEDULE_NAME => 'disk_full_sched')
DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
CHAIN_NAME => 'bulk_load_chain',
STEP_NAME => 'load_data_evt',
EVENT_CONDITION =>
'tab.user_data.object_owner=''HR'' and
tab.user_data.object_name = ''DATA.TXT'' and
tab.user_data.event_type =''FILE_ARRIVAL'' ',
QUEUE_SPEC => 'HR.LOAD_JOB_EVENT_Q')

3. Define chain rules. Each rule has a condition and an action.

If the condition evaluates to TRUE, the action is performed. Conditions are usually based on the outcome of one or more previous steps. A condition accepts Boolean and numeric integer values in an expression.

The entire expression must evaluate to a Boolean value.

The simplified syntax of a chain condition is as follows:

'factor|NOT(factor)[AND|OR factor]'

factor:

stepname ERROR_CODE number|[NOT]step_condition

When creating a rule condition using the simplified syntax:

• You specify one or more factors, and a Boolean operator (AND, OR, or NOT).

• A factor can be either a simple Boolean value (TRUE or FALSE) or a chain condition. A chain condition describes the condition of another step in the job chain. You can use the following to describe the chain condition:

o The current state of the chain step:

  • SUCCEEDED

  • FAILED

  • STOPPED

  • COMPLETED

o The error code returned by the chain step. The error is a numeric value, and can be:

  • Evaluated within a numeric clause

  • Compared to a list of values using an IN clause

You can use negative factors, by enclosing the factor in parentheses and prefixing the factor with the NOT operator.

Examples:

'step1 SUCCEEDED AND step2 ERROR_CODE = 3'
'TRUE'
'step3 NOT COMPLETED AND NOT (step1 SUCCEEDED)'
'step2 ERROR_CODE NOT IN (1,2,3)'

You can also refer to attributes of chain steps of the chain (this is called bind-variable syntax). The syntax is as follows:

STEP_NAME.ATTRIBUTE

• Possible attributes are: completed, state start_date, end_date, error_code, and duration.

• Possible values for the state attribute include:

'NOT_STARTED', 'SCHEDULED', 'RUNNING',
'PAUSED', 'SUCCEEDED', 'FAILED', and
'STOPPED'.

• If a step is in the state 'SUCCEEDED', 'FAILED', or 'STOPPED', its completed attribute is set to 'TRUE'; otherwise, completed is 'FALSE'.

Some examples of the bind variable syntax are:

':step1.state=''SUCCEEDED'' and
:step2.error_code=3'
'1=1'
':step3.state != ''COMPLETED'''
':step2.error_code not in (1,2,3)'
':step1.state = ''NOT_STARTED'''

The rule action specifies what is to be done as a result of the rule being triggered. A typical action is to run a specified step. Possible actions include:

o START step_1[,step_2...]
o STOP step_1[,step_2...]
o END [{end_value | step_name.error_code}]

When the job starts and at the end of each step, all rules are evaluated to see what action or actions occur next. You can also configure rules to be evaluated at regular intervals by using the EVALUATION_INTERVAL attribute of the chain.

You add a rule to a chain with the DEFINE_CHAIN_RULE procedure:

BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => 'bulk_load_chain',
CONDITION => 'TRUE', -- starting step
ACTION => 'START load_data_evt,
stop_when_disk_full_evt',
Rule_Name => 'dataload_rule1',
COMMENTS => 'start the chain');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
CHAIN_NAME => 'bulk_load_chain',
CONDITION => 'load_data_evt COMPLETED',
ACTION => 'START do_bulk_load',
RULE_NAME => 'dataload_rule2');
END;

4. Enable a chain with the ENABLE procedure (A chain is always created disabled). Enabling an already enabled chain does not return an error.

DBMS_SCHEDULER.ENABLE ('bulk_load_chain');

5. To run a chain, you must create a job of type 'CHAIN'. The job action must refer to the chain name.

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'bulk_load_chain_job',
job_type => 'CHAIN',
job_action => 'bulk_load_chain',
repeat_interval => 'freq=daily;byhour=7',
enabled => TRUE);
END;

Oracle job chaining example script

The job_chain_aq.sql script cbelow 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.
 
SQL> exec dbms_scheduler.run_job ('job_chain_aq_task_3');
 
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
07-AUG-2004 18:26:04.972000 TASK_3
 
 
SQL> @job_chain_aq_query.sql
 
no rows selected

 
If manually attempting to start jobs out of sequence, the sessions hang until the appropriate message is sent.


 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

 

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

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster