Re: Replacing crons with jobs...

Jose Luis Delgado wrote:
>
> List...
>
> How can I translate this:
>
> CRONTAB:
> 00 8-19 * * 1,2,3,4,5
> /litoral/oracle/admin/get_users.sh
>
> 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!
>
> TIA
> JL
>

Jose,

   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',
  						   'NLS_LANGUAGE=AMERICAN'),
  						'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 ...

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).