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

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Wed, 26 Oct 2005 10:22:03 -0400
Message-ID: <4001DEAF7DF9BD498B58B45051FBEA6502E09B90@25exch1.vicorpower.vicr.com>

Tony,  

    I've not tried to be so sophisticated, but the following has worked  

interval=> 'decode(rtrim(to_char(sysdate,''DAY'')),''SUNDAY'', sysdate+1,''SATURDAY'',sysdate+1, sysdate+.5)'  

Watch the quotes else you get syntax errors. My best was to place the decode in a variable and then set interval =>'&nd'

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of t_adolph_at_hotmail.com Sent: Wednesday, October 26, 2005 11:15 AM To: ORACLE-L
Subject: schedule a job every 15 mins *but* only during the office hours

Hi all,

I'm trying to setup snapshots to run every 15 minutes during working hours,
else hourly. I'm ignoring weekends for now,...  

Origonally I tried:  

var job number;
begin
 sys.dbms_job.submit(
  job => :job,
    what => 'statspack.snap();',
    next_date => trunc(sysdate,'HH24') + 1/24,     interval => case

          when (to_char(sysdate,'hh24') between 8 and 20) then
           'trunc(sysdate,''MI'') + 15/1440' -- 15 mins
          else
           'trunc(sysdate,''HH24'') + 1/24'
        end

 );
  commit;
end;
/  

but it seems that interval will only be set once, i.e. when the dbms_job is actually submitted.
And not each "run", the evaluation of the case statement is stored and not the statement
its self.  

I then tried using a function instead of sql, one which returned a date string for the next time to
run. But it is only evaluated the once as above.  

I tried a similar approach with next_date, but got syntax errors.  

Can it be done this way? I've written a work-round using a wrapper for snap():
I schedule procedure every 15 min and it works out whether it should call a snap or not:  

CREATE OR REPLACE PROCEDURE snapIf(

 p_now date default sysdate,
 p_startOfDay_HH24 number default 8,
 p_endOfDay_HH24 number default 17, -- includes 17:59
 p_startOfWeekD number default 2, -- Monday
 p_endOfWeekD number default 6 -- Friday

 ) is
begin
  if (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

--dbms_output.put_line('snap during week');
      statspack.snap();
  elsif to_char(p_now,'MI') = 0 -- on the hour outside office hours   then

--dbms_output.put_line('snap on the hour');
      statspack.snap();
  else

--dbms_output.put_line('no snap');
      null;
  end if;
end;  

Any thoughts...  

Cheers
Tony

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 26 2005 - 09:24:35 CDT