DBMS_SCHEDULER [message #313366] |
Fri, 11 April 2008 09:12 |
Drimcynn
Messages: 2 Registered: April 2008
|
Junior Member |
|
|
I've been a dba for years and have used dbms_scheduler for a couple of years now but nothing elaborate. just the basic jobs similar to dbms_job.
I'd like to create a job that runs every 15 minutes between the hours of 5:30am and 10pm daily. Can anyone help in this matter.
I assume I got to do something with the windows and schedules but after reading up on it, I'm just as confused on the details and syntax. the job every 15 mins is no biggie but between the hours is what I am struggling with.
Thanks in advance for the help.
|
|
|
|
|
|
Re: DBMS_SCHEDULER [message #313430 is a reply to message #313366] |
Sat, 12 April 2008 01:14 |
|
Michel Cadot
Messages: 68712 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
A generic answer is to use a schedule function like the following (sorry for French comment) that allows an execution every nbMin minutes between 7:00 and 8:30 and 14:00 and 16:00 from monday to friday:
create or replace function NextJobDate (nbMin number) return date is
/*------------------------------------------------------------*/
/* */
/* Paramètre : nbMin=nombre de minutes pour le prochain job */
/* */
/* heures d'activation : */
/* - de 7h00 à 8h30 */
/* - de 14h00 à 16h00 */
/* jours d'activation : lundi à vendredi */
/* */
/*------------------------------------------------------------*/
now date := sysdate;
begin
if to_number(to_char(now,'D')) = to_number(to_char(to_date('03/07/2004','DD/MM/YYYY'),'D'))
then /* samedi -> lundi matin */
return trunc(now)+2+7/24;
elsif to_number(to_char(now,'D')) = to_number(to_char(to_date('04/07/2004','DD/MM/YYYY'),'D'))
then /* dimanche -> lundi matin */
return trunc(now)+1+7/24;
elsif to_number(to_char(now,'HH24')) < 7 then /* avant 7h00 -> prochain=7h00 */
return trunc(now)+7/24;
elsif ( to_number(to_char(now,'HH24')) = 8 and to_number(to_char(now,'MI')) >= 30 )
or ( to_number(to_char(now,'HH24')) > 8 and to_number(to_char(now,'HH24')) < 14 ) then
/* après 8h30 -> prochain=14h00 */
return trunc(now)+14/24;
elsif to_number(to_char(now,'HH24')) >= 16 then
/* après 16h -> prochain=lendemain ou le prochain lundi 7h */
if to_number(to_char(now,'D')) = to_number(to_char(to_date('02/07/2004','DD/MM/YYYY'),'D'))
then /* Vendredi -> lundi matin */
return trunc(now)+3+7/24;
else /* -> demain matin */
return trunc(now)+1+7/24;
end if;
else /* Sinon dans nbMin minutes */
return now+nbMin/24/60;
end if;
end;
/
Then you just have to put "NextJobDate(15)" for your interval if you want to start the job every 15 minutes during these periods.
Of course you can replace by any other one you want for your own schedule and even rebuild the schedule string syntax of Oracle new dbms_scheduler.
Regards
Michel
|
|
|