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:52:06 +0100
Message-ID: <BAY103-DAV9A69BAC5E75D17F8F3E23FA690@phx.gbl>

Ophs, my explanation / feedback has a typo

I got the quotes wron again, the line should of read:
"It seems I fell over because I didn't quote the case call (interval => 'case
"

Original Message ----- From: t_adolph_at_hotmail.com To: ORACLE-L Sent: Wednesday, October 26, 2005 5:41 PM Subject: Re: schedule a job every 15 mins *but* only during the office hours

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:53:20 CDT