Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_SCHEDULER (10gr2)
DBMS_SCHEDULER [message #313366] Fri, 11 April 2008 09:12 Go to next message
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 #313369 is a reply to message #313366] Fri, 11 April 2008 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
IMO, the simplest way to accomplish this is to have the job itself check the current time at the start of the task.
If it is before 05:30 or after 22:00, then exit immediately without doing any work.
Re: DBMS_SCHEDULER [message #313370 is a reply to message #313369] Fri, 11 April 2008 09:30 Go to previous messageGo to next message
Drimcynn
Messages: 2
Registered: April 2008
Junior Member
*smacks his forehead*

I didn't even think of attempting that. Smile Thanks.
Re: DBMS_SCHEDULER [message #313422 is a reply to message #313370] Fri, 11 April 2008 20:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
or, for your interval:

'case when to_char (sysdate, ''hh24'') < 22
      then sysdate + 15/(24*60) 
      else trunc (sysdate) + 1 + 5.5/24
 end'

Re: DBMS_SCHEDULER [message #313430 is a reply to message #313366] Sat, 12 April 2008 01:14 Go to previous message
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
Previous Topic: Query for Finding the Date History
Next Topic: Help on desired output
Goto Forum:
  


Current Time: Thu Nov 14 05:04:21 CST 2024