Oracle Scheduling Complex Date Rules for
Job Execution

Complex Date Rules for Job Execution - By Dr. Tim Hall

Prior to Oracle10g, the only way to define a jobs repeat interval was to use a PL/SQL expression that evaluated to a date.  In Oracle10g and beyond, the calendar syntax is the preferred way to define a jobs repeat interval, although PL/SQL expression can still be used if they evaluate to a timestamp.  In this section we will compare how each method works.

The previous section used the test_calendar_string.sql procedure to display the run schedule expected for a specific calendar string.  Before any comparisons between the possible scheduling methods can be done, a way to test the PL/SQL expressions that are used to schedule jobs using dates and timestamps is needed. 

The test_date_string procedure listed below is similar to the test_calendar_string procedure, but it displays run dates defined by interval strings that might be used when scheduling jobs via the dbms_job package.

* test_date_string.sql

set serveroutput on;
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

CREATE OR REPLACE PROCEDURE test_date_string(
  p_interval    IN  VARCHAR2,
  p_iterations  IN  NUMBER DEFAULT 5)
AS
  l_interval           VARCHAR2(1000) := p_interval;
  l_start_date         DATE := TO_DATE('01-JAN-2004 03:04:32',
                                       'DD-MON-YYYY HH24:MI:SS');
  l_next_run_date      DATE;
  l_start_date_str     VARCHAR2(100);
BEGIN
See download for full script
  FOR i IN 1 .. p_iterations LOOP
    l_start_date_str := ?TO_DATE(??? ||
                         TO_CHAR(l_start_date, ?DD-MON-YYYY HH24:MI:SS?) ||
                         ???,??DD-MON-YYYY HH24:MI:SS??)?;
    l_interval := REPLACE(LOWER(p_interval), ?sysdate?, l_start_date_str);
    EXECUTE IMMEDIATE ?SELECT ? || l_interval || ? INTO :return FROM dual?
      INTO l_next_run_date;   

    DBMS_OUTPUT.put_line('Next Run Date: ' || l_next_run_date);
    l_start_date := l_next_run_date;
  END LOOP;
END;
/

The test_timestamp_string procedure listed below is a copy of the test_date_string procedure that has been adjusted to work with timestamps.

* test_timestamp_string.sql

-- *************************************************
-- Copyright ? 2015 by Rampant TechPress
-- *************************************************

set serveroutput on;
alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';

CREATE OR REPLACE PROCEDURE test_timestamp_string(
  p_interval    IN  VARCHAR2,
  p_iterations  IN  NUMBER DEFAULT 5)
AS
  l_interval         VARCHAR2(1000) := p_interval;
  l_start_ts         TIMESTAMP := TO_TIMESTAMP('01-JAN-2004 03:04:32',
                                               'DD-MON-YYYY HH24:MI:SS');  l_next_run_ts      TIMESTAMP;
  l_start_ts_str     VARCHAR2(100);
BEGIN
See download for full script
  FOR i IN 1 .. p_iterations LOOP
    l_start_ts_str := ?TO_TIMESTAMP(??? ||
                       TO_CHAR(l_start_ts, ?DD-MON-YYYY HH24:MI:SS?) ||
                       ???,??DD-MON-YYYY HH24:MI:SS??)?;
    l_interval := REPLACE(LOWER(p_interval), ?systimestamp?, l_start_ts_str);
    EXECUTE IMMEDIATE ?SELECT ? || l_interval || ? INTO :return FROM dual?
      INTO l_next_run_ts;   

    DBMS_OUTPUT.put_line('Next Run Date: ' || l_next_run_ts);
    l_start_ts := l_next_run_ts;
  END LOOP;
END;
/

The best way to come to grips with defining repeat intervals and comparing the different methods available is looking at some examples.  Table 3.11 below lists a range of repeat intervals along with expressions than can be used to achieve them. 

The date expressions can be used to schedule jobs using the dbms_job package, while the timestamp and calendar syntax expressions can be used for jobs scheduled using the dbms_scheduler package.  Where possible, a literal and interval literal example is given along with an example of the output generated by the test procedures.

Run job Every day.

?sysdate + 1?
?systimestamp + 1?

?sysdate + interval ??1?? day?
?systimestamp + interval ??1?? day?

?freq=daily;?

Next Run Date: 02-JAN-2004 03:04:32
Next Run Date: 03-JAN-2004 03:04:32
Next Run Date: 04-JAN-2004 03:04:32
Next Run Date: 05-JAN-2004 03:04:32
Next Run Date: 06-JAN-2004 03:04:32

Midnight every night.

?trunc(sysdate) + 1?
?trunc(systimestamp) + 1?Run job

?trunc(sysdate) + interval ??1?? day?
?trunc(systimestamp) + interval ??1?? day?

?freq=daily; byhour=0; byminute=0; bysecond=0;?

Next Run Date: 02-JAN-2004 00:00:00
Next Run Date: 03-JAN-2004 00:00:00
Next Run Date: 04-JAN-2004 00:00:00
Next Run Date: 05-JAN-2004 00:00:00
Next Run Date: 06-JAN-2004 00:00:00

Run job 6:00 AM every day.

?trunc(sysdate) + 1 + 6/24?
?trunc(systimestamp) + 1 + 6/24?

?trunc(sysdate) + interval ??1 6?? day to hour ?
?trunc(systimestamp) + interval ??1 6?? day to hour?

?freq=daily; byhour=6; byminute=0; bysecond=0;?

Next Run Date: 01-JAN-2004 06:00:00
Next Run Date: 02-JAN-2004 06:00:00
Next Run Date: 03-JAN-2004 06:00:00
Next Run Date: 04-JAN-2004 06:00:00
Next Run Date: 05-JAN-2004 06:00:00

Run job Every hour.

?sysdate + 1/24?
?systimestamp + 1/24?

?sysdate + interval ??1?? hour?
?systimestamp + interval ??1?? hour?

?freq=hourly;?

Next Run Date: 01-JAN-2004 04:04:32
Next Run Date: 01-JAN-2004 05:04:32
Next Run Date: 01-JAN-2004 06:04:32
Next Run Date: 01-JAN-2004 07:04:32
Next Run Date: 01-JAN-2004 08:04:32

Run job Every hour, on the hour.

?trunc(sysdate, ??HH24??) + 1/24?
?trunc(systimestamp, ??HH24??) + 1/24?

?trunc(sysdate, ??HH24??) + interval ??1?? hour?
?trunc(systimestamp, ??HH24??) + interval ??1?? hour?

?freq=hourly; byminute=0; bysecond=0;?

Next Run Date: 01-JAN-2004 04:00:00
Next Run Date: 01-JAN-2004 05:00:00
Next Run Date: 01-JAN-2004 06:00:00
Next Run Date: 01-JAN-2004 07:00:00
Next Run Date: 01-JAN-2004 08:00:00

Run job Every minute.

?sysdate + 1/24/60?
?systimestamp + 1/24/60?

?sysdate + interval ??1?? minute?
?systimestamp + interval ??1?? minute?

?freq=minutely;?

Next Run Date: 01-JAN-2004 03:05:32
Next Run Date: 01-JAN-2004 03:06:32
Next Run Date: 01-JAN-2004 03:07:32
Next Run Date: 01-JAN-2004 03:08:32
Next Run Date: 01-JAN-2004 03:09:32

Run job Every minute, on the minute.

?trunc(sysdate, ??MI??) + 1/24/60?
?trunc(systimestamp, ??MI??) + 1/24/60?

See download for full script

?trunc(sysdate, ??MI??) + interval ??1?? minute?
?trunc(systimestamp, ??MI??) + interval ??1?? minute?

?freq=minutely; bysecond=0;?

Next Run Date: 01-JAN-2004 03:05:00
Next Run Date: 01-JAN-2004 03:06:00
Next Run Date: 01-JAN-2004 03:07:00
Next Run Date: 01-JAN-2004 03:08:00
Next Run Date: 01-JAN-2004 03:09:00

Run job Every hour.

?sysdate + 1/24?
?systimestamp + 1/24?

See download for full script

?sysdate + interval ??1?? hour?
?systimestamp + interval ??1?? hour?

?freq=hourly;?

Next Run Date: 01-JAN-2004 04:04:32
Next Run Date: 01-JAN-2004 05:04:32
Next Run Date: 01-JAN-2004 06:04:32
Next Run Date: 01-JAN-2004 07:04:32
Next Run Date: 01-JAN-2004 08:04:32

Run job Every hour, on the hour.

?trunc(sysdate, ??HH24??) + 1/24?
?trunc(systimestamp, ??HH24??) + 1/24?

See download for full script

?trunc(sysdate, ??HH24??) + interval ??1?? hour?
?trunc(systimestamp, ??HH24??) + interval ??1?? hour?

?freq=hourly; byminute=0; bysecond=0;?

Next Run Date: 01-JAN-2004 04:00:00
Next Run Date: 01-JAN-2004 05:00:00
Next Run Date: 01-JAN-2004 06:00:00
Next Run Date: 01-JAN-2004 07:00:00
Next Run Date: 01-JAN-2004 08:00:00

Run job Every Monday at 9:00 AM

?trunc(next_day(sysdate, ??MONDAY??)) + 9/24?
?trunc(next_day(systimestamp, ??MONDAY??)) + 9/24?

See download for full script

?trunc(next_day(sysdate, ??MONDAY??)) + interval ??9?? hour?
?trunc(next_day(systimestamp, ??MONDAY??)) + interval ??9??hour?

?freq=weekly; byday=mon; byhour=9; byminute=0; bysecond=0;?

Next Run Date: 05-JAN-2004 09:00:00
Next Run Date: 12-JAN-2004 09:00:00
Next Run Date: 19-JAN-2004 09:00:00
Next Run Date: 26-JAN-2004 09:00:00
Next Run Date: 02-FEB-2004 09:00:00

Run job Every Monday, Wednesday and Friday at 6:00 AM

?trunc(least(next_day(sysdate, ??monday??), next_day(sysdate, ??wednesday??), next_day(sysdate, ??friday??))) + (6/24)?

See download for full script

?trunc(least(next_day(systimestamp, ??monday??), next_day(systimestamp, ??wednesday??), next_day(systimestamp, ??friday??))) + (6/24)?

?trunc(least(next_day(sysdate,??monday??), next_day(sysdate, ??wednesday??), next_day(sysdate, ??friday??))) + interval ??6?? hour?

?trunc(least(next_day(systimestamp, ??monday??), next_day(systimestamp, ??wednesday??), next_day(systimestamp, ??friday??))) + interval ??6?? hour?

 ?freq=weekly; byday=mon,wed,fri; byhour=6; byminute=0; bysecond=0;?

Next Run Date: 02-JAN-2004 06:00:00
Next Run Date: 05-JAN-2004 06:00:00
Next Run Date: 07-JAN-2004 06:00:00
Next Run Date: 09-JAN-2004 06:00:00
Next Run Date: 12-JAN-2004 06:00:00

Run job First Monday of each quarter

?next_day(add_months(trunc(sysdate, ??q??), 3), ??monday??)?
 See download for full script
?next_day(add_months(trunc(systimestamp, ??q??), 3), ??monday??)?

?freq=monthly; bymonth=1,4,7,10; byday=1mon?

Next Run Date: 05-APR-2004 00:00:00
Next Run Date: 05-JUL-2004 00:00:00
Next Run Date: 04-OCT-2004 00:00:00
Next Run Date: 03-JAN-2005 00:00:00
Next Run Date: 04-APR-2005 00:00:00

Table 3.11 - Repeat intervals and the expressions than can be used to achieve them. 

It would appear that all of the above expressions give exactly the same run schedule regardless of which syntax is used.  In practice this not true because PL/SQL expressions can allow the run schedules of jobs to slide.

The scheduler attempts to execute all jobs on time, but in practice there is often a small delay.  When a job is executed, the first thing that happens is the next run date is calculated using the specified repeat interval.  Since most PL/SQL expressions use either the sysdate or systimestamp functions, the actual start date may be slightly later than the jobs original next run date.  Over several iterations of the job, this could add up to a noticeable difference between the times the job is expected to run and when it actually does run.  Table 3.12 gives an example of the sort of slide that might be seen if the scheduler is consistently 10 seconds late in executing a job that was originally intended to run a 09:00:00 each day.

Table 3.12 ? The time slide phenomenon

This issue becomes even more noticeable on shorter repeat intervals such as hourly runs. This behavior can be prevented by always defining PL/SQL expressions that result in a specific time, rather than one relative to the current time.  This is typically done using the trunc and round functions to remove the variable components. For example, ?trunc(sysdate) + 1 + 6/24? is always 06:00 tomorrow morning no matter what time it is evaluated because the time component has been truncated.  The earlier examples regularly make use of the trunc function for the same reason.

The calendar syntax does not suffer from the problem of sliding schedules as the repeat intervals it defines are always time specific.  If a component of the calendar string is not defined explicitly, it is defaulted using values from the start date specified when the job or schedule was defined.  For example, a schedule with a start date of 01-JAN-2004 09:45:31 and a calendar string with no byminute clause would actually be assigned byminute=45.  As a result, every next_run_date evaluated using this schedule would have a value of 45 minutes past the hour.

Sometimes it is either not possible or very difficult to define a repeat interval using the calendar syntax or a PL/SQL expression.  In these situations, it might be easier to use a database function which returns a date or timestamp as required.  The my_schedule_function.sql script creates a function which returns a different time interval depending on the contents of the database.

my_schedule_function.sql

-- *************************************************
-- Copyright ? 2015 by Rampant TechPress

-- Requires the following grant:
--   grant select on v_$database to job_user;
-- **********************************************************

CREATE OR REPLACE FUNCTION my_schedule_function (
  p_timestamp  IN  TIMESTAMP)
  RETURN TIMESTAMP
AS
  l_db_name    v$database.name%TYPE;
  l_timestamp  TIMESTAMP;
BEGIN
  See download for full script
  SELECT name
  INTO   l_db_name
  FROM   v$database; 

  CASE l_db_name
    WHEN 'PROD' THEN l_timestamp := p_timestamp + INTERVAL '10' MINUTE;
    WHEN 'TEST' THEN l_timestamp := p_timestamp + INTERVAL '1' HOUR;
    ELSE l_timestamp := p_timestamp + INTERVAL '1' DAY;
  END CASE; 

  RETURN l_timestamp;
END;
/

When this script is run against the development environment with a database name of DB10G, the following run schedule is produced:

SQL1> exec test_timestamp_string('my_schedule_function(systimestamp)');

Next Run Date: 02-JAN-2004 03:04:32
Next Run Date: 03-JAN-2004 03:04:32
Next Run Date: 04-JAN-2004 03:04:32
Next Run Date: 05-JAN-2004 03:04:32
Next Run Date: 06-JAN-2004 03:04:32

The same result could be achieved by running a different schedule in each environment, but it serves to illustrate the point.

It should now be obvious that there is an almost limitless combination of possible calendar string and PL/SQL expression variations.  The only way to become really confident with repeat intervals is to try as many variations as possible.  The test procedures presented in this chapter will allow this to be done without having to actually schedule jobs, which in turn will save lots of time.

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.