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:41:40 +0100
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:
