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

  • From: <t_adolph@xxxxxxxxxxx>
  • To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 26 Oct 2005 17:52:06 +0100

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@xxxxxxxxxxx 
  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

Other related posts: