Raj,
You must be speaking
from "UNIX heights" -J
Under Windows I find
dbms_job much more reliable than windows "at" scheduling.
Actually, never had
problems with dbms_job "forgetting" to run a job.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-----Original
Message-----
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni,
Rajendra
Sent: Wednesday,
July 23, 2003 9:24 AM
To:
Multiple recipients of list ORACLE-L
Subject: RE: DBMS_JOB
scheduling
Garry,
1. have you tried select to_char(sysdate,'D') from
dual ??
This is really nice, but my only gripe with dbms-job
is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look
at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time
and the workaround was like setting job_processes to a very large
number.
nevertheless, I think what you have attempted is
fantastic and worthy of adoption ...
Raj
--------------------------------------------------------------------------------
Rajendra dot Jamadagni at
nospamespn dot com
All Views expressed in this email are strictly
personal.
QOTD:
Any clod can have facts, having an opinion is an art !
-----Original Message-----
From: Garry Gillies [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 23, 2003
5:59 AM
To:
Multiple recipients of list ORACLE-L
Subject: DBMS_JOB scheduling
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]
