Oracle 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.
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 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 semicolon.
-
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 countback.
As such,
bymonthday=-1 equates to the
last day of the month. The documentation
states that countbacks 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 the 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.
