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 Conditional Job Runs Using a Custom Table Solution

Oracle Tips by Burleson Consulting

Conditional Job Runs using a Custom Table Solution

If none of the previous methods seem suitable, a specific solution to meet specific needs can always be built..  The following example could be used as a starting point for such a solution.

The sequence of jobs is protected using the job_chain_locks table.  The RETRIES column specifies the number of times a task should check the locks before it gives up and re-schedules itself.  The RETRY_DELAY column specifies the number of minutes between retries.  The TASK_NAME and LOCKED columns are self-explanatory.

* job_chain_locks.sql

CREATE TABLE job_chain_locks (
  task_name    VARCHAR2(20)               NOT NULL,
  locked       VARCHAR2(1)   DEFAULT 'Y'  NOT NULL,
  retries      NUMBER(3)     DEFAULT 0    NOT NULL,
  retry_delay  NUMBER(3)     DEFAULT 1    NOT NULL,
  CONSTRAINT job_chain_locks_pk PRIMARY KEY (task_name)
);

INSERT INTO job_chain_locks (task_name, locked, retries, retry_delay)
VALUES ('task_2', 'Y', 5, 1);

INSERT INTO job_chain_locks (task_name, locked, retries, retry_delay)
VALUES ('task_3', 'Y', 3, 1);

COMMIT;

The contents of the job_chain_locks table can be monitored using the job_chain_locks_query.sql script.

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

select
   *
from
   job_chain_locks
order by
   task_name
;

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

* job_chain_custom_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_custom AS

PROCEDURE task_1;
PROCEDURE task_2;
PROCEDURE task_3;
PROCEDURE lock_task (p_task_name  IN  job_chain_locks.task_name%TYPE,
                     p_lock       IN  BOOLEAN DEFAULT TRUE);
FUNCTION unlocked (p_task_name  IN  job_chain_locks.task_name%TYPE)
  RETURN BOOLEAN;

END job_chain_custom;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY job_chain_custom 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 unlock task_2
  lock_task ('task_2', FALSE); 

EXCEPTION
  WHEN OTHERS THEN
    -- Don't unlock task_2.
    NULL;
END task_1;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
PROCEDURE task_2 AS
-- -----------------------------------------------------------------
BEGIN

  IF unlocked('task_2') THEN
    lock_task ('task_2');

    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 unlock task_3
    lock_task ('task_3', FALSE);
  END IF;

EXCEPTION
  WHEN OTHERS THEN
    -- Don't unlock task_3.
    NULL;
END task_2;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
PROCEDURE task_3 AS
-- -----------------------------------------------------------------
BEGIN

  IF unlocked('task_3') THEN
    lock_task ('task_3');

    INSERT INTO job_chain (created_timestamp, task_name)
    VALUES (systimestamp, 'TASK_3');
    COMMIT;
  END IF;

END task_3;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
PROCEDURE lock_task (p_task_name  IN  job_chain_locks.task_name%TYPE,
                     p_lock       IN  BOOLEAN DEFAULT TRUE) AS
-- -----------------------------------------------------------------
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE job_chain_locks
  SET    locked = 'Y'
  WHERE  task_name = p_task_name;
  COMMIT;
END lock_task;
-- -----------------------------------------------------------------

-- -----------------------------------------------------------------
FUNCTION unlocked (p_task_name  IN  job_chain_locks.task_name%TYPE)
  RETURN BOOLEAN AS
-- -----------------------------------------------------------------
  l_jcl_row  job_chain_locks%ROWTYPE;

BEGIN
  SELECT *
  INTO   l_jcl_row
  FROM   job_chain_locks
  WHERE  task_name = p_task_name; 

  IF l_jcl_row.locked != 'Y' THEN
    RETURN TRUE;
  END IF;

  FOR i IN 1 .. l_jcl_row.retries LOOP
    DBMS_LOCK.sleep(60 * l_jcl_row.retry_delay);

    SELECT locked
    INTO   l_jcl_row.locked
    FROM   job_chain_locks
    WHERE  task_name = p_task_name;

    IF l_jcl_row.locked != 'Y' THEN
      RETURN TRUE;
    END IF;
  END LOOP; 

  RETURN FALSE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
END unlocked;
-- -----------------------------------------------------------------

END job_chain_custom;
/
SHOW ERRORS

Next, the jobs associated with each task are scheduled.

* job_chain_custom_jobs.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_custom_task_1',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'job_chain_custom.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_custom_task_2',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'job_chain_custom.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_custom_task_3',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'job_chain_custom.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;
/

-- Pre Oracle10g

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

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

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

  COMMIT;
END;
/
*/

-- Cleanup

/*
-- Oracle10g
BEGIN
  DBMS_SCHEDULER.drop_job ('job_chain_custom_task_3');
  DBMS_SCHEDULER.drop_job ('job_chain_custom_task_2');
  DBMS_SCHEDULER.drop_job ('job_chain_custom_task_1');
END;
/

-- Pre Oracle10g

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

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

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

PL/SQL procedure successfully completed.

SQL> @job_chain_query.sql

CREATED_TIMESTAMP           TASK_NAME
--------------------------- --------------------
07-AUG-2004 19:54:51.010000 TASK_1

1 row selected.

SQL> @job_chain_locks_query.sql

TASK_NAME            L    RETRIES RETRY_DELAY
-------------------- - ---------- -----------
task_2               N          5           1
task_3               Y          3           1

2 rows selected.

Running the second job manually results in the second task being relocked and the third task being unlocked.

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

PL/SQL procedure successfully completed.

SQL> @job_chain_query.sql

CREATED_TIMESTAMP           TASK_NAME
--------------------------- --------------------
07-AUG-2004 19:54:51.010000 TASK_1
07-AUG-2004 19:57:29.636000 TASK_2

2 rows selected.

SQL> @job_chain_locks_query.sql

TASK_NAME            L    RETRIES RETRY_DELAY
-------------------- - ---------- -----------
task_2               Y          5           1
task_3               N          3           1

2 rows selected.

Running the third job manually results in the second task being relocked.

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

PL/SQL procedure successfully completed.

SQL> @job_chain_query.sql

CREATED_TIMESTAMP           TASK_NAME
--------------------------- --------------------
07-AUG-2004 19:54:51.010000 TASK_1
07-AUG-2004 19:57:29.636000 TASK_2
07-AUG-2004 19:59:11.184000 TASK_3

3 rows selected.

SQL> @job_chain_locks_query.sql

TASK_NAME            L    RETRIES RETRY_DELAY
-------------------- - ---------- -----------
task_2               Y          5           1
task_3               Y          3           1

2 rows selected.

Attempting to run a job out of order will result in the session hanging until the task is unlocked or the appropriate number of retries has been attempted; at which point the job is rescheduled.

SQL> set timing on

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

PL/SQL procedure successfully completed.

Elapsed: 00:03:04.50

The DBA now has a variety of tools available with which to build job chains.  The next section will introduce the error handling requirements associated with job scheduling.

 

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