Oracle 10g Scheduling Calendar Syntax

Calendar Syntax in Oracle10g

Oracle10g introduced a calendar syntax allowing complex job execution cycles to be defined in a simple and clear manner.  The calendar syntax is listed below:

repeat_interval = freq=?
  [; interval=?] [; bymonth=?] [; byweekno=?]
  [; byyearday=?] [; bymonthday=?] [; byday=?]
  [; byhour=?] [; byminute=?] [; bysecond=?]

Before investigating what the individual clauses of this syntax mean, how the calendar strings can be tested should be explained.  The evaluate_calendar_string procedure from the dbms_scheduler package returns run timestamps by evaluating a specified calendar string.

PROCEDURE evaluate_calendar_string (
   calendar_string    IN  VACRHAR2,
   start_date         IN  TIMESTAMP WITH TIME ZONE,
   return_date_after  IN  TIMESTAMP WITH TIME ZONE,
   next_run_date      OUT TIMESTAMP WITH TIME ZONE);

The parameters associated with this procedure and their usage are as follows:

* calendar_string - The calendar string to be evaluated.

* start_date - The date the calendar string becomes valid.  If elements of the calendar string are missing, they may be derived from elements of this date.

* return_after_date - Only dates after this date will be returned by the procedure.  If no date is specified, the current systimestamp is used.

* next_run_date - The first date that matches the calendar_string and start_date and is greater than the run_after_date.

The test_calendar_string.sql procedure, listed below, uses the evaluate_calendar_string procedure to display a list of run dates.  For convenience, the start_date and run_after_date parameters are defaulted.

*
test_calendar_string.sql

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

CREATE OR REPLACE PROCEDURE test_calendar_string(
  p_calendar_string  IN  VARCHAR2,
  p_iterations       IN  NUMBER DEFAULT 5)
AS
  l_start_date         TIMESTAMP := TO_TIMESTAMP('01-JAN-2004 03:04:32',
                                               'DD-MON-YYYY HH24:MI:SS');
  l_return_date_after  TIMESTAMP := l_start_date;
  l_next_run_date      TIMESTAMP;
BEGIN
  FOR i IN 1 .. p_iterations LOOP
    DBMS_SCHEDULER.evaluate_calendar_string ( 
      calendar_string   => p_calendar_string,
      start_date        => l_start_date,
      return_date_after => l_return_date_after,
      next_run_date     => l_next_run_date);

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

The individual clauses that make up a calendar string are explained in Table 3.10 below.  The test_calendar_string procedure is used to display sample run timestamps to make the element usage clear.

SQL> -- Run every second.
SQL> exec test_calendar_string('freq=secondly');
Next Run Date: 01-JAN-2004 03:04:33
Next Run Date: 01-JAN-2004 03:04:34
Next Run Date: 01-JAN-2004 03:04:35
Next Run Date: 01-JAN-2004 03:04:36
Next Run Date: 01-JAN-2004 03:04:37

PL/SQL procedure successfully completed.

interval

Specifies the interval associated with the specified frequency, such that an interval of 2 on a minutely frequency would cause the job to execute every other minute, while an interval of 10 would cause it to execute every 10 minutes.  The allowable values are 1 to 999, with a default value of 1.

SQL> -- Run every 30 minutes
SQL> exec test_calendar_string('freq=minutely; interval=30');
Next Run Date: 01-JAN-2004 03:34:32
Next Run Date: 01-JAN-2004 04:04:32
Next Run Date: 01-JAN-2004 04:34:32
Next Run Date: 01-JAN-2004 05:04:32
Next Run Date: 01-JAN-2004 05:34:32

PL/SQL procedure successfully completed.

bymonth

Specifies the month or months on which the job should execute. The month can be specified using the month number (1-12) or using the three letter abbreviation (jan-dec).

byweekno

Specifies the week or weeks of the year to include for strings with a yearly frequency.  Allowable values are one to 52 (or 53), with the week starting on a Monday and finishing on a Sunday.

byyearday

Specifies the day or days of the year with valid values from one to 366.

bymonthday

Specifies the day or days of the month with valid values from one to 31.  Using a negative number results in a count backwards from the end of the month such that   -1 means the last day of the month.

byday

Specifies the day or days of the week with reference to the frequency using the three letter abbreviation. When used with a yearly or monthly frequency, the day can be prefixed with a number.  Prefixing the day with a number indicates the occurrence of that number within the frequency, with negative numbers providing a backwards count.

byhour

Specifies the hour or hours of the day with valid values from zero to 23.  Negative numbers result in a backwards count.

byminute

Specifies the minute or minutes of the hour with valid values from zero to 59.

bysecond

Specifies the second or seconds of the minute with valid values from zero to 59.  Negative numbers result in a backwards count.

Table 3.10 ? Calendar parameters and their meanings

The following points contain general guidance information for the use of calendar syntax during scheduling:

* The calendar string must contain a frequency as the first clause.  All other clauses are optional and can be placed in any order.

* Each clause can only be present once and must be separated by a semi-colon.

* The calendar strings are not case sensitive and white spaces between clauses are allowed.

* Where a BY clause contains a list of values, the order of the list is not important.

* When there are not enough clauses to determine the precise run date, the missing clauses are derived from the start_date.  For example, if there is no bysecond clause in the calendar string, the value of seconds from the start_date is used to create one.

* When a number range is not fixed, the last value of the range can be determined using a negative integer as a count-back.  As such bymonthday=-1 equates to the last day of the month. The documentation states that count-backs are not supported for fixed number ranges such as those used by the bymonth, byhour, byminute and bysecond clauses, but they do appear to work consistently.

* The first day of the week is Monday.

* A calendar string cannot specify time zones.  Instead the time zone is derived from one of the following places in this order: the start_date, the current session?s time zone, the DEFAULT_TIMEZONE scheduler attribute, or time zone returned by the systimestamp function.

Now that calendar syntax has been introduced in detail, the following section will compare the use of PL/SQL expressions and the use of calendar syntax for scheduling jobs.

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.