Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Replacing crons with jobs...

Re: Replacing crons with jobs...

From: Stephane Faroult <>
Date: Mon, 19 May 2003 12:02:06 -0800
Message-ID: <>

Jose Luis Delgado wrote:
> List...
> How can I translate this:
> 00 8-19 * * 1,2,3,4,5
> /litoral/oracle/admin/
> to Oracle with DBMS_JOB?
> I mean: I got confused at the time when I tried to
> program a job from certain days at certain hours
> (intervals).
> ie: monday to friday from 8:00AM to 19:00 PM every
> hour.
> of course, the *.sh file, connect to Oracle and
> executes a *.sql script file.
> Can you give a bit of help!
> JL


   Difficult to give general guidelines (converting automatically from cron to Oracle is another of my numerous fancies) but I *think* that the following expression matches your example. I hope that the comments following it can at least give you ideas about various tricks you can use.

decode(sign((trunc(sysdate) + 19/24 - 1/1440 - sysdate) *

  			(sysdate - (trunc(sysdate) + 8/24))),
  			  -1, trunc(sysdate +
  				    decode(to_char(sysdate, 'DAY',
  						'FRIDAY', 3,
  						'SATURDAY', 2,
  							  1)) + 8/24,
  			      round(sysdate + 1 / 24, 'HH24'))

First, I use the sign() function to determine whether I am in the 8-19 bracket. I multiply the difference between 7pm and now by the difference between now and 8am, which gives me a positive number if I am in the interval and a negative one outside it. As ever, the boundaries are a problem, so I substract 1 minute (1/1440 day) to the upper bound so that 7pm behaves like anything later and only 8am returns 0 for the product. Now with this result, if I get 0 (8am) or 1 (9am to 6pm) I just have to add one hour to the current date. I always round up (in that case to the nearest hour) because of uncertainties about the exact moment when the job fires.
Now, if I get -1 (7pm to 7am), I must add the proper number of days, which depends on the current one. Note that I force the language to be AMERICAN so that I can check for the day name without fearing that the default language is something else (English, for instance :-)). If it's Friday I add three days, I handle the Saturday case because you can always expect people to run things when they shouldnt, and in all the other cases I add one day, I truncate to 00:00, add another 8 hours and I'm done.

Not guaranteed error-free ...


Stephane Faroult
Oriole Software
Please see the official ORACLE-L FAQ:
Author: Stephane Faroult

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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).
Received on Mon May 19 2003 - 15:02:06 CDT

Original text of this message