Oracle
Complex Date Rules for Job Execution
Oracle Tips by Burleson Consulting
Advanced Oracle Utilities: The Definitive Reference by
Rampant TechPress is written by four of the top Oracle
database experts (Bert Scalzo, Donald
Burleson, and Steve Callan). The following is an excerpt
from the book.
Prior to Oracle, the only way to define a job’s
repeat interval was to use a PL/SQL expression
that evaluated to a date.
In Oracle, the calendar syntax is the
preferred way to define a job’s repeat interval,
although PL/SQL expression can still be used if
they evaluate to a timestamp.
In this section, each method will be
covered and compared.
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.
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
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
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
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 11.8 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 and the timestamp and calendar syntax
expressions can be used for jobs scheduled using
the
dbms_scheduler
package in
Oracle.
Where possible, a literal and interval
literal example is given along with an example
of the output generated by the test procedures.
|
INTERVAL
|
EXPRESSION
|
|
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’
‘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
|
|
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
|
|
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
|
|
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
|
|
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
|
|
Every minute, on the
minute
|
‘trunc(sysdate,
‘’MI’’) + 1/24/60’
‘trunc(systimestamp,
‘’MI’’) + 1/24/60’
‘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
|
|
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
|
|
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
|
|
Every Monday at 9:00
AM
|
‘trunc(next_day(sysdate,
‘’MONDAY’’)) + 9/24’
‘trunc(next_day(systimestamp,
‘’MONDAY’’)) + 9/24’
‘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
|
|
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)’
‘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
|
|
First Monday of each
quarter
|
‘next_day(add_months(trunc(sysdate,
‘‘q’’), 3), ‘‘monday’’)’
‘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 11.8 -
Repeat Intervals with Their
Expressions
|
|
|
