Re: dbms_jobs once an hour at the bottom of the hour

Syltrem wrote:
>
> "Michel Cadot" <micadot{at}altern{dot}org> wrote in message
> news:43835ac5$0$4331$626a54ce_at_news.free.fr...
> >
> > "Syltrem" <syltremzulu_at_videotron.ca> a écrit dans le message de news:
> > 11o69aean6e1p06_at_corp.supernews.com...
> > |
> > | "Michel Cadot" <micadot{at}altern{dot}org> wrote in message
> > | news:4382157f$0$4360$626a54ce_at_news.free.fr...
> > | > | >
> > | > | > dbms_job.submit (..., next_date=>trunc(sysdate,'HH')+3/24/2,
> > | > | > interval=>'trunc(sysdate,''HH'')+3/24/2',...);
> > | > | >
> > | > | > Regards
> > | > | > Michel Cadot
> > | > | >
> > | > | >
> > | > |
> > | > | How did you come up with this?
> > | > | I see the trunc(sysdate,'HH') comes up with the current hour but the
> > | > 3/24/2
> > | > | is a mystery to me.
> > | > |
> > | > |
> > | >
> > | > Well, 1 is a day, 1/24 is one hour, 3/24/2 is 1.5 hour.
> > | > So truncating to the hour and adding 1.5 hour give me
> > | > 30 minutes after the next hour.
> > | >
> > |
> > | Bonjour Michel
> > |
> > | Would you also have a clever way of getting the time that would
> > represent
> > | the next half hour?
> > | I can't find a way...
> > | 1:00, then 1:30, then 2:00, 2:30, etc for running a job every 30
> > minutes.
> > |
> > | Thanks
> > | Syltrem
> > |
> >
> > trunc(sysdate,'HH')+decode(sign(to_number(to_char(sysdate,'MI'))-30),1,2,1)/48
> >
> > Regards
> > Michel Cadot
> >
> >
>
> Great ! Clever indeed, using SIGN...
> Thanks !

You can have full control over the timing by passing "next_date" to the procedure itself. ie,

dbms_job.submit(:j, 'my_proc(next_date);')

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------