Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: schedule a job every 15 mins *but* only during the office hours

Re: schedule a job every 15 mins *but* only during the office hours

From: <t_adolph_at_hotmail.com>
Date: Wed, 26 Oct 2005 17:41:40 +0100
Message-ID: <BAY103-DAV11FD6499B7D3D4E74B5189FA690@phx.gbl>


Hi all,

Thanks for all the replies.

It seems I fell over because I didn't quote the case call (interval => case '....'), thanks to those who pointed this out for me.

I tried to quote the case statement as you suggested Malcolm, but after loads of double, tripple and quad quotes I gave up. Always got an error.

It was also pointed out that I could try to put the case statement (or similar) in the job part: what => case when ....

I found the "cleanest" solution was to write a simple function to return the date for the interval. I tried this earlier (as I put in my original post) *but* forgot to quote the function call, so it so it was being evaluated *then* passed to the submit_job :-(

So those of you still awake :-) IMHO this is the most elegant approach:

CREATE OR REPLACE FUNCTION next_snap_due(

 p_now date default sysdate,
 p_startOfDay_HH24 number default 8,
 p_endOfDay_HH24 number default 17, -- Note: includes 17:59
 p_interval_minutes number default 15, 

  p_startOfWeekD number default 2, -- Monday   p_endOfWeekD number default 6 -- Friday)   )
  return date
is
begin
 return case
   when (to_char(p_now,'HH24') between
           p_startOfDay_HH24 and p_endOfDay_HH24)
       and (to_char(p_now,'D') between
           p_startOfWeekD and p_endOfWeekD)
      then

    trunc(sysdate,'MI') + p_interval_minutes/1440    else
    trunc(sysdate,'HH24') + 1/24
   end;
end;

var job number;
begin
 sys.dbms_job.submit(
  job => :job,
    what => 'statspack.snap();',
    next_date => sysdate + 1/24,
    interval => 'next_snap_due()' -- dont forget the quotes  );
  commit;
end;
/

Thanks again for all of the replies,
Cheers
Tony Adolph

PS: just as an aside... I noticed that a couple of replies and including an AskTom reply that I was directed to used date models 'DY' or 'DAY'. I've founds this potentially problematic. All that a user has to do is change the NLS settings for something other than AMERICAN_* or ENGLISH_* and all it all goes pair shaped! More portable (or less breakable) to use 'D' where 1 = Sunday, Sontag, Dimanche,... etc :-) PPS: I think you can include an explicit NLS_LANG setting in your dbms job

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 26 2005 - 10:42:52 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US