Oracle dbms_job: job have to run only weekday 6am-8pm

Question

i have an oracle 10g Database with different jobs. One of thoses jobs have to run every two hours. Therefore the "next_dat" and "interval" parameters looks like that.

   ,next_date => to_date('27.04.2011 10:18:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'to_date(to_char(sysdate + (2/24), ''DD.MM.YYYY HH24:MI''), ''DD.MM.YYYY HH24:MI'')'

The jobs don't need to run at night. So the question is: is it possible to design for example "interval" in that way, that the jobs only runs between 6am and 8pm (in hh24 06:00 until 20:00).

My first idea (and last choice) is an addition job which sets the main-job to "broken" between 8pm an 6 am. But i don't like the idea of an different job.

Thank you!

Nick Pierpoint · Accepted Answer · 2011-04-27T09:09:39.777

You could use a case statement in the interval to check for the time of day then skip:

case
  when to_char(trunc(sysdate, 'HH24') + 2/24, 'HH24') between '06' and '20' then
    trunc(sysdate, 'HH24') + 2/24 
  else
    trunc(sysdate)+1+(6/24)
end

This can be expanded to include a check on weekday. (You need to be careful on checking for the day of week as it is dependent on the NLS settings.)

Something like the following would handle the weekday, but you'll have to confirm the edge cases and check your NLS settings (for me, Monday is day 1 and Sunday is day 7):

case
  when
    to_number(to_char(sysdate, 'D')) between 1 and 5 then
      case
        when to_char(trunc(sysdate, 'HH24') + 2/24, 'HH24') between '06' and '20'
                                                                            then 
          trunc(sysdate, 'HH24') + 2/24 
        else 
          trunc(sysdate)+1+(6/24)
        end
  else
    trunc(sysdate) + (8 - to_number(to_char(sysdate, 'D'))) + 6/24
end

Although... think we are now firmly in the "time to right a function" stage. :)

oh wow, that's looking great. right now i'm trying to verify the first solution.....please give some minutes. — The Bndr, Apr 27 '11 at 09:14
yeah, that's it. Thank you so much. At first I will realize the "check 06-20' Version. This first step is an great benefit for me. I will check the performance at weekend an maybe go the 2nd step (only weekdays) later. Thank you!! — The Bndr, Apr 27 '11 at 09:19

score 2 · Answer 2 · answered Apr 27 '11 at 08:34

2

Make the interval a function, then you can have whatever control you like.

See here for an example:

http://www.orafaq.com/node/871

answered Apr 27 '11 at 08:34

cagcowboy

30,012
11
69
93

Thank you for that idea. I will save the posted link for future use. – The Bndr Apr 27 '11 at 09:20

Oracle dbms_job: job have to run only weekday 6am-8pm

2 Answers2