Any Interest?
The DBMS_JOB package is supplied by Oracle to allow the running of
procedures at regular
intervals. Unfortunately the INTERVAL parameter is limited to 128
characters, which prevents
you from getting very complex (user defined functions [in the interval
parameter] do not
work well - according to Fuerstein in his book Oracle Built In Packages).
The situation is eased somewhat by the fact that the NEXT_DATE parameter
can be supplied
to the procedure as an in/out parameter - and the procedure can contain
whatever code is
necessary to calculate when next to run.
This is all very well, but custom coding scheduling routines can quickly
become tedious.
On the basis of "do it once and get it over with" I have written a
function called NEXT_DATE
which I have wrapped in a package called CRON.
There is a Unix program called cron which runs jobs on a regular basis.
Although the scheduling
data supplied to cron is simple and concise, complex schedules are easy
to specify.
The NEXT_DATE function takes in a cron schedule string and returns the
next date that
conforms to the schedule - or you can supply a cron schedule and a date
and it will return the
first date after the supplied date that conforms to the schedule.
At the moment it is not very friendly on the error detection front. A
VALUE_ERROR is
returned if it deems the cron schedule to be invalid. You will also get a
VALUE_ERROR
if the next valid date is more than twenty seven years in the future.
DBMS_OUTPUT is used to display error messages which will hopefully give
you a clue.
This will be improved if I receive enough complaints ( and suggestions for
improvements).
THE CRON SCHEDULE
A cron schedule consists of five components, each separated from the next
by a space.
The syntax is identical for all components.
The components represent
Minute in Hour
Hour in day
Day in month
Month in year
Day of Week - A bit of a bugger this one. In Unix land the day
numbering runs
from 0-6 with 0 being Sunday. In Oracle the day numbering depends
on the
setting of NLS_TERRITORY.
I have chosen to go with ISO standard 8601:1998 which runs from
1-7
with 1 being Monday. This is so close to the Unix convention that
I can interpret
Unix cron schedules correctly.
Curiously, Oracle do not provide a date format which supplies
this number.
The ISO week number is available with the format 'IW', but not
the ISO day
number. If you have a field of type date called dt, you can
obtain the ISO day
number with
( trunc(dt) - trunc(dt ,'IW') ) + 1
A component can consist of
an asterisk * which represents all valid values
or
a number of elements separated by a comma (if only one element is
supplied,
forget the comma). An element can be
a single number - valid for the component (32 in "Day in month" is
invalid)
or
two numbers separated by a hyphen - which represents a range.
EXAMPLES
Run every hour on the hour
0 * * * *
Run twice every hour, on the hour and on the half hour
0,30 * * * *
Run twice every hour, on the hour and on the half hour between 08:00 and
16:59
0,30 8-16 * * *
Run twice every hour, on the hour and on the half hour between 08:00 and
16:59, Monday to Friday
0,30 8-16 * * 1-5
Run at 11:12 every Friday the 13th
11 12 13 * 5
Run at 04:00 every leap year on february 29
0 4 29 2 *
Run at 04:00 every leap year on february 29 when february 29 is a Thursday
0 4 29 2 4
Garry Gillies
Database Administrator
Business Systems
Weir Pumps Ltd
149 Newlands Road, Cathcart, Glasgow, G44 4EX
T: +44 0141 308 3982
F: +44 0141 633 1147
E: [EMAIL PROTECTED]
CONFIDENTIAL:
The information contained in this email (including any attachments)
is confidential, subject to copyright and for the use of the
intended recipient only. If you are not the intended recipient
please delete this message after notifying the sender. Unauthorised
retention, alteration or distribution of this email is forbidden
and may be actionable.
Attachments are opened at your own risk and you are advised to scan
incoming email for viruses before opening any attached files. We
give no guarantee that any communication is virus-free and accept
no responsibility for virus contamination or other system loss or
damage of any kind.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Garry Gillies
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
