Re: DBMS_JOB hourly interval

In article <353350a5.0109131316.36b12e2a_at_posting.google.com>, abkirch_at_excite.com says...
>
>If anyone is looking to schedule a job using an hourly interval here
>is the value for that parameter.
>
>DECODE(
> SIGN(15 - to_char( sysdate,'MI'))
> , 1
> , sysdate + 60 + (15 - to_char(sysdate,'MI')) /1440
> , -1
> , sysdate + (60 + (15 - to_char(sysdate,'MI')))/1440
> , sysdate
> )
>
>Use this value for both the NEXT_DATE and the INTERVAL parameters to
>prevent your job from sliding.
>

that seems to be adding 60 DAYS if the sign is +1??

and the second one for -1 will "slide" -- its not based off of a known, fixed time.

To avoid slides, you almost always need to use TRUNC to get a "known" time.

>I thought I'd post this since I was looking for it the last few days
>but finally I worked through it and got. If someone else got in an
>easier fashion go ahead and post it.

how about:

trunc(sysdate,'hh')+1/24

and

'trunc(sysdate,''hh'')+1/24'

Now, no matter when the interval is computed -- it will be at the top of the NEXT hour.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp