From oracle-l-bounce@freelists.org Wed Oct 26 10:53:20 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9QFrFWl003896 for ; Wed, 26 Oct 2005 10:53:20 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9QFrAvX003867 for ; Wed, 26 Oct 2005 10:53:10 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B604720F9E4; Wed, 26 Oct 2005 10:52:59 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26592-06; Wed, 26 Oct 2005 10:52:59 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 294C420F2A8; Wed, 26 Oct 2005 10:52:59 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 26 Oct 2005 10:51:03 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1442E20FC67 for ; Wed, 26 Oct 2005 10:51:03 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26181-08 for ; Wed, 26 Oct 2005 10:51:02 -0500 (EST) Received: from hotmail.com (bay103-dav9.bay103.hotmail.com [65.54.174.81]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5A99F210096 for ; Wed, 26 Oct 2005 10:51:02 -0500 (EST) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Wed, 26 Oct 2005 08:51:01 -0700 Message-ID: Received: from 213.54.150.241 by BAY103-DAV9.phx.gbl with DAV; Wed, 26 Oct 2005 15:51:01 +0000 X-Originating-IP: [213.54.150.241] X-Originating-Email: [t_adolph@hotmail.com] X-Sender: t_adolph@hotmail.com From: To: "ORACLE-L" References: <4001DEAF7DF9BD498B58B45051FBEA6502E09B90@25exch1.vicorpower.vicr.com> Subject: Re: schedule a job every 15 mins *but* only during the office hours Date: Wed, 26 Oct 2005 17:52:06 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0952_01C5DA55.FAA12FB0" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 X-OriginalArrivalTime: 26 Oct 2005 15:51:01.0560 (UTC) FILETIME=[10931380:01C5DA45] X-archive-position: 27625 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: t_adolph@hotmail.com Precedence: normal Reply-To: t_adolph@hotmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-3.7 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE, MSGID_FROM_MTA_HEADER,NO_REAL_NAME,RCVD_IN_NJABL,RCVD_IN_SORBS autolearn=no version=2.63 ------=_NextPart_000_0952_01C5DA55.FAA12FB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 = =3D> 'case=20 "=20 ----- Original Message -----=20 From: t_adolph@hotmail.com=20 To: ORACLE-L=20 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 = =3D> case '....'), thanks=20 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:=20 what =3D> 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,=20 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 =3D> :job, what =3D> 'statspack.snap();', next_date =3D> sysdate + 1/24, interval =3D> '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=20 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=20 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 =3D Sunday, Sontag, Dimanche,... etc :-)=20 PPS: I think you can include an explicit NLS_LANG setting in your dbms = job ------=_NextPart_000_0952_01C5DA55.FAA12FB0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Ophs, my explanation / feedback has a=20 typo
 
I got the quotes wron again, the line = should of=20 read:
"It seems I fell over because I didn't = quote the=20 case call (interval =3D> 'case
" 
----- Original Message -----
From:=20 t_adolph@hotmail.com
Sent: Wednesday, October 26, = 2005 5:41=20 PM
Subject: Re: schedule a job = every 15 mins=20 *but* only during the office hours

Hi all,
 
Thanks for all the = replies.
 
It seems I fell over because I didn't = quote the=20 case call (interval =3D> case '....'), thanks
to those who = pointed this=20 out for me.
 
I tried to quote the case statement = as you=20 suggested Malcolm, but after loads of double,
tripple and quad = quotes I=20 gave up.  Always got an error.
 
It was also pointed out that I could = try to put=20 the case statement (or similar) in the job part:
what =3D> case = when=20 ...
 
I found the "cleanest" solution was = to write a=20 simple function to return the date for the
interval.  I tried = this=20 earlier (as I put in my original post) *but* forgot to quote = the
function=20 call, so it so it was being evaluated *then* passed to the submit_job=20 :-(
 
So those of you still awake :-)  = IMHO this=20 is the most elegant approach:
 
CREATE OR REPLACE FUNCTION=20 next_snap_due(
 p_now date default = sysdate,
 p_startOfDay_HH24=20 number default 8,
 p_endOfDay_HH24 number default 17, -- Note: = includes 17:59
 p_interval_minutes number default 15, =
 =20 p_startOfWeekD number default 2, -- Monday
  p_endOfWeekD = number=20 default 6 -- Friday)
  )
  return=20 date
is
begin
 return case
   when=20 (to_char(p_now,'HH24')=20 = between
          =20 p_startOfDay_HH24 and = p_endOfDay_HH24)
      =20 and (to_char(p_now,'D')=20 = between
          =20 p_startOfWeekD and p_endOfWeekD)
     =20 then
    trunc(sysdate,'MI') +=20 = p_interval_minutes/1440
   else
   &n= bsp;trunc(sysdate,'HH24')=20 + 1/24
   end;
end;
 
var job=20 number;
begin
 sys.dbms_job.submit(
  job = =3D>=20 :job,
    what =3D>=20 'statspack.snap();',
    next_date =3D> sysdate + = 1/24,
    interval =3D> 'next_snap_due()' -- dont = forget=20 the quotes
 );
  commit;
end;
/
 
Thanks again for all of the=20 replies,
Cheers
Tony Adolph
 
PS:  just as an aside... I = noticed that a=20 couple of replies and including
an AskTom reply that I was = directed to=20 used date models 'DY' or 'DAY'.
I've founds this potentially=20 problematic.  All that a user has to do is
change the NLS = settings=20 for something other than AMERICAN_* or ENGLISH_*
and all it all = goes pair=20 shaped!  More portable (or less breakable) to
use 'D' where 1 = =3D=20 Sunday, Sontag, Dimanche,...  etc :-)
PPS: I think you can = include an=20 explicit NLS_LANG setting in your dbms=20 job
------=_NextPart_000_0952_01C5DA55.FAA12FB0-- -- http://www.freelists.org/webpage/oracle-l