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 


 

 

 


 

 

 

 

 

Conditional Job Enabling tips

Oracle Tips by Burleson Consulting

Conditional Job Enabling

In this method, all tasks in the chain are scheduled as regular repeating jobs, but only the first job in the chain is enabled.  All subsequent jobs in the chain are disabled or marked as broken prior to Oracle10g.  As each task in the chain completes successfully, it enables the next task in the chain by enabling its associated job.  Every time the first task runs, it disables the chain before starting again.

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

* job_chain_enable.sql


CREATE OR REPLACE PACKAGE job_chain_enable AS

PROCEDURE task_1;
PROCEDURE task_2;
PROCEDURE task_3;

END job_chain_enable;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY job_chain_enable AS

-- -----------------------------------------------------------------
PROCEDURE task_1 AS
-- -----------------------------------------------------------------
BEGIN

  -- Disable dependant jobs
  -- Oracle10g
  DBMS_SCHEDULER.disable ('job_chain_enable_task_2');
  DBMS_SCHEDULER.disable ('job_chain_enable_task_3');

  -- Pre Oracle10g
  /*
  DBMS_JOB.broken (1001, TRUE);
  DBMS_JOB.broken (1002, TRUE);
  COMMIT;
  */

  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 enable task_2
  -- Oracle10g
  DBMS_SCHEDULER.enable ('job_chain_enable_task_2');
   
  -- Pre Oracle10g
  /*
  DBMS_JOB.broken (1001, FALSE, SYSDATE + INTERVAL '2' MINUTE);
  COMMIT;
  */

EXCEPTION
  WHEN OTHERS THEN
    -- Don't enable 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 enable task_3
  -- Oracle10g
  DBMS_SCHEDULER.enable ('job_chain_enable_task_3');

  -- Pre Oracle10g
  /*
  DBMS_JOB.broken (1002, FALSE, SYSDATE + INTERVAL '2' MINUTE);
  COMMIT;
  */

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

Since no jobs are created by the code, they must all be created in advance using the job_chain_enable_jobs.sql script.  The jobs must persist, so they are generated with repeat intervals.  These repeat intervals schedule them to run at 06:00, 12:00 and 18:00 respectively.  Commands to remove the jobs are included and should be run once the example is completed.

* job_chain_enable_jobs.sql

-- Oracle10g

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'job_chain_enable_task_1',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'job_chain_enable.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 enable chain.');
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'job_chain_enable_task_2',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'job_chain_enable.task_2',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=12; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => FALSE,
    comments        => 'Second task in the enable chain.');
END;
/

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'job_chain_enable_task_3',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'job_chain_enable.task_3',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=18; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => FALSE,
    comments        => 'Third task in the enable chain.');
END;
/

-- Pre Oracle10g
/*

BEGIN
  DBMS_JOB.isubmit (
    job       => 1000,
    what      => 'BEGIN job_chain_create.task_1; END;',
    next_date => SYSDATE,
    interval  => 'TRUNC(SYSDATE) + INTERVAL ''1 6'' DAY TO HOUR');

  DBMS_JOB.isubmit (
    job       => 1001,
    what      => 'BEGIN job_chain_create.task_2; END;',
    next_date => SYSDATE,
    interval  => 'TRUNC(SYSDATE) + INTERVAL ''1 12'' DAY TO HOUR');

  DBMS_JOB.broken(1001, TRUE); 

  DBMS_JOB.isubmit (
    job       => 1002,
    what      => 'BEGIN job_chain_create.task_3; END;',
    next_date => SYSDATE,
    interval  => 'TRUNC(SYSDATE) + INTERVAL ''1 18'' DAY TO HOUR');

  DBMS_JOB.broken(1002, TRUE);

  COMMIT;
END;
/
*/

-- Cleanup
/*
-- Oracle10g

BEGIN
  DBMS_SCHEDULER.drop_job ('job_chain_enable_task_3');
  DBMS_SCHEDULER.drop_job ('job_chain_enable_task_2');
  DBMS_SCHEDULER.drop_job ('job_chain_enable_task_1');
END;
/

-- Pre Oracle10g

BEGIN
  DBMS_JOB.remove(1002);
  DBMS_JOB.remove(1001);
  DBMS_JOB.remove(1000);
  COMMIT;
END;
/
*/

The current job schedules for this example can be queried using the job_queue_query.sql script listed below.

* job_queue_query.sql

set feedback off
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS.FF';
alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM';
set feedback on

set linesize 100
column created_timestamp format a27
column next_run_date format a34
column next_date format a20

prompt
prompt USER_SCHEDULER_JOBS
select
   job_name,
   enabled,
   next_run_date
from
   user_scheduler_jobs
order by
   job_name
;

prompt USER_JOBS
select
   job,
   broken,
   next_date
from
   user_jobs
order by
   job
;

The output of this script along with the output of the job_chain_query.sql script is listed below.

SQL> @job_chain_query.sql

no rows selected

SQL> @job_queue_query.sql

USER_SCHEDULER_JOBS

JOB_NAME                    ENABL NEXT_RUN_DATE
--------------------------- ----- ----------------------------------
JOB_CHAIN_ENABLE_TASK_1     TRUE  08-AUG-2004 06:00:00.800000 +01:00
JOB_CHAIN_ENABLE_TASK_2     FALSE
JOB_CHAIN_ENABLE_TASK_3     FALSE

3 rows selected.

At this point, the first task is scheduled but has not been executed, hence no results in the job_chain table.  Rather than waiting until 6:00, it can be forced to run immediately.  The results below show that the first task has run, and the second job has been enabled.

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

PL/SQL procedure successfully completed.

SQL> @job_queue_query.sql

USER_SCHEDULER_JOBS

JOB_NAME                    ENABL NEXT_RUN_DATE
--------------------------- ----- ----------------------------------
JOB_CHAIN_ENABLE_TASK_1     TRUE  08-AUG-2004 06:00:00.800000 +01:00
JOB_CHAIN_ENABLE_TASK_2     TRUE  08-AUG-2004 12:00:00.200000 +01:00
JOB_CHAIN_ENABLE_TASK_3     FALSE

3 rows selected.

USER_JOBS

no rows selected

SQL> @job_chain_query.sql

CREATED_TIMESTAMP           TASK_NAME
--------------------------- --------------------
07-AUG-2004 13:52:28.227000 TASK_1

1 row selected.

Next, run the second job manually.  The results below show that the second task has run and the third job has been enabled.

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

PL/SQL procedure successfully completed.

SQL> @job_chain_query.sql

CREATED_TIMESTAMP           TASK_NAME
--------------------------- --------------------
07-AUG-2004 13:52:28.227000 TASK_1
07-AUG-2004 13:59:16.666000 TASK_2

2 rows selected.

SQL> @job_queue_query.sql

USER_SCHEDULER_JOBS

JOB_NAME                   ENABL NEXT_RUN_DATE
-------------------------- ----- ----------------------------------
JOB_CHAIN_ENABLE_TASK_1    TRUE  08-AUG-2004 06:00:00.800000 +01:00
JOB_CHAIN_ENABLE_TASK_2    TRUE  08-AUG-2004 12:00:00.200000 +01:00
JOB_CHAIN_ENABLE_TASK_3    TRUE  07-AUG-2004 18:00:00.700000 +01:00

3 rows selected.

USER_JOBS

no rows selected

Next, run the third job manually.  The results below show that the third task has run successfully.

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

PL/SQL procedure successfully completed.

SQL> @job_chain_query.sql

CREATED_TIMESTAMP           TASK_NAME
--------------------------- --------------------
07-AUG-2004 13:52:28.227000 TASK_1
07-AUG-2004 13:59:16.666000 TASK_2
07-AUG-2004 14:02:10.948000 TASK_3

3 rows selected.

SQL> @job_queue_query.sql

USER_SCHEDULER_JOBS

JOB_NAME                    ENABL NEXT_RUN_DATE
--------------------------- ----- ----------------------------------
JOB_CHAIN_ENABLE_TASK_1     TRUE  08-AUG-2004 06:00:00.800000 +01:00
JOB_CHAIN_ENABLE_TASK_2     TRUE  08-AUG-2004 12:00:00.200000 +01:00
JOB_CHAIN_ENABLE_TASK_3     TRUE  07-AUG-2004 18:00:00.700000 +01:00

3 rows selected.

USER_JOBS

no rows selected

Finally, run the first job again to see that the subsequent jobs have been enabled or disabled appropriately.

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

PL/SQL procedure successfully completed.

SQL> @job_chain_query.sql

CREATED_TIMESTAMP           TASK_NAME
--------------------------- --------------------
07-AUG-2004 14:03:55.683000 TASK_1

1 row selected.

SQL> @job_queue_query.sql

USER_SCHEDULER_JOBS

JOB_NAME                    ENABL NEXT_RUN_DATE
--------------------------- ----- ----------------------------------
JOB_CHAIN_ENABLE_TASK_1     TRUE  08-AUG-2004 06:00:00.800000 +01:00
JOB_CHAIN_ENABLE_TASK_2     TRUE  08-AUG-2004 12:00:00.700000 +01:00
JOB_CHAIN_ENABLE_TASK_3     FALSE 07-AUG-2004 18:00:00.700000 +01:00

3 rows selected.

USER_JOBS

no rows selected

Care must be taken when running the pre-10g version of this code due to the way the broken procedure works.  When a job has its broken flag set to FALSE, its next run date is set to the value specified by the next_date parameter.  If this is not specified, it defaults to the current datetime.  As a result, the enabled job will not run at the expected time.  In this example, the next_date parameter has been specified as a two minute interval for the convenience of testing, but in a real example, it must be set to an appropriate datetime value.

 

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