Chapter 3 - Time-Based Job Scheduling
||Oracle Tips by Burleson
Calendar Syntax in
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
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
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.
Complex Date Rules
for Job Execution
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, 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.
Download your Oracle scripts now:
definitive Oracle Script collection for every Oracle professional DBA
Copyright © 1996 - 2014 by
Burleson. All rights reserved.
Oracle® is the registered trademark
of Oracle Corporation.