Re: DBMS_JOB.INTERVAL help needed

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 26 Mar 2005 09:55:24 +0100
Message-ID: <42452327$0$32091$626a14ce@news.free.fr>

"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 Received on Sat Mar 26 2005 - 02:55:24 CST