Re: DBMS_JOB.INTERVAL help needed

Michel Cadot wrote:
> "Randy Harris" <randy_at_SpamFree.com> a écrit dans le message de
> news:eL81e.18443$BW5.2146_at_newssvr31.news.prodigy.com...
> | I'm trying to manage the interval with DBMS_JOB. I'd like to set a
range of
> | hours and limit the days.
> |
> | I tried developing my own function and using that, but couldn't get
it to
> | work. Is that supposed to be possible? I see in Tom Kyte's web
site, he
> | has an example that uses a CASE statement. I'm still working with
8i and
> | have no CASE statement. Should I be able to use IF-THEN? What
built-in
> | functions are supposed to be available? The SQL functions? The
PL/SQL
> | functions? I'd appreciate a point in the right direction.
> |
> | --
> | Randy Harris
> | (tech at promail dot com)
> |
> |
>
> For instance, to run a job every 8Am Monday through Friday and 10Am
on Saturday:
>
> create or replace function nextjobdate return date is
> now date := sysdate;
> begin
> if

to_number(to_char(now,'D'))=to_number(to_char(to_date('02/07/2004','DD/MM/YYYY'),'D')) then
> /* friday */
> if to_number(to_char(now,'HH24')) < 8 then
> /* before 8h -> next 8h */
> return trunc(now)+8/24;
> else /* after 8h -> tomorrow morning 10h */
> return trunc(now)+1+10/24;
> end if;
> elsif

to_number(to_char(now,'D'))=to_number(to_char(to_date('03/07/2004','DD/MM/YYYY'),'D')) then
> /* saturday */
> if to_number(to_char(now,'HH24')) < 10 then
> /* before 10h -> next 10h */
> return trunc(now)+10/24;
> else /* after 10h -> monday morning */
> return trunc(now)+2+8/24;
> end if;
> elsif to_number(to_char(now,'D')) =

to_number(to_char(to_date('04/07/2004','DD/MM/YYYY'),'D'))
> then /* sunday -> monday morning */
> return trunc(now)+1+8/24;
> elsif to_number(to_char(now,'HH24')) < 8 then
> /* before 8h -> next 8h */
> return trunc(now)+8/24;
> else /* tomorrow */
> return trunc(now)+1+8/24;
> end if;
> end;
> /
>
> exec dbms_job.submit (:jobno, 'myjob;', nextjobdate, 'nextjobdate;');
>
> Regards
> Michel Cadot

If next interval is not too complicated, you could also make use of DECODE, TRUNC and simple date arithmatic to calculate next interval without resorting to PLSQL.

Regards
/Rauf Received on Sat Mar 26 2005 - 07:20:39 CST