{"id":324,"date":"2016-07-27T10:45:34","date_gmt":"2016-07-27T14:45:34","guid":{"rendered":"http:\/\/dbtricks.com\/?p=324"},"modified":"2016-07-27T10:45:34","modified_gmt":"2016-07-27T14:45:34","slug":"how-to-schedule-a-job-to-run-on-a-different-hour-on-a-specific-date","status":"publish","type":"post","link":"https:\/\/dbtricks.com\/?p=324","title":{"rendered":"How to schedule a job to run on a different hour on a specific date."},"content":{"rendered":"

Oracle jobs are a simple way to schedule procedure and processes. However, sometimes the scheduling requirements can get really complicated. A very common request is to run a job on a specific hour on weekdays and not to run it on the weekend. This request can be easily delivered using the “WHAT”<\/strong> parameter of the dbms_job.submit procedure.<\/p>\n

\r\nbegin\r\n  if to_char(sysdate, 'D') between 2 and 6 then\r\n      my_procedure ;\r\n  end if;\r\nend;\r\n<\/pre>\n
However, what will you do if you need to run the procedure at a specific hour on Monday to Friday but on a different hour on the weekend?
\nIn this case you will be happy to know that you can use CASE  on the “INTERVAL”<\/strong> parameter of the  dbms_job.submit procedure.<\/p>\n
\r\ndeclare\r\n  my_job number;\r\nbegin\r\n  dbms_job.submit(job => my_job, \r\n    what => 'my_procedure ;',\r\n    \r\n     interval => 'case\r\n                     when  to_number(to_char(sysdate, ''D'')) between 1 and 5 then \r\n                        TRUNC(SYSDATE+1) + 16 \/24 \r\n                     when  to_number(to_char(sysdate, ''D'')) between 6 and 7 then\r\n                         TRUNC(SYSDATE+1) + 8 \/24 \r\n                  end'\r\n                      \r\n   \r\n    );\r\nend;\r\n<\/pre>\n
remember that this calculation is for NEXT run not for the current. Therefore, you will need to take into consideration that the timinig for Monday will be calculated on Sunday (I know it sounds obvious but you will be surprised how many time you can do the same mistake).<\/p>\n
While the case clause can handle many calculations and complications, there is one caveat. The case statement can handle only 200 characters. This becomes a problem when you try to write a longer statement. For example:<\/p>\n
\r\ncase\r\n  when  to_number(to_char(sysdate, 'D')) between 1 and 4 then \r\n      TRUNC(SYSDATE+1) + 16 \/24 \r\n  when  to_number(to_char(sysdate, 'D'))= 5 then\r\n      TRUNC(SYSDATE+1) + 13 \/24     \r\n  when  to_number(to_char(sysdate, 'D'))= 6 then\r\n      TRUNC(SYSDATE+2) + 16 \/24          \r\n  else\r\n      TRUNC(SYSDATE+1) + 16 \/24 \r\n end\r\n<\/pre>\n
if you try to submit a job with this statement, you will probably get the following error<\/p>\n
\r\nORA-12899: value too large for column \"SYS\".\"JOB$\".\"INTERVAL#\" (actual: 257, maximum: 200)\r\nORA-06512: at \"SYS.DBMS_IJOB\", line 290\r\nORA-06512: at \"SYS.DBMS_JOB\", line 159\r\nORA-06512: at line 4\r\n<\/pre>\n
You can workaround this problem if you use a function to calculate the INTERVAL <\/strong>value.<\/p>\n
\r\ncreate or replace function get_next_run_date return date is\r\n\r\nres date;\r\nbegin\r\nselect\r\n\r\ncase\r\n  when  to_number(to_char(sysdate, 'D')) between 1 and 4 then\r\n      TRUNC(SYSDATE+1) + 16 \/24\r\n  when  to_number(to_char(sysdate, 'D'))= 5 then\r\n      TRUNC(SYSDATE+1) + 13 \/24\r\n  when  to_number(to_char(sysdate, 'D'))= 6 then\r\n      TRUNC(SYSDATE+2) + 16 \/24\r\n  else\r\n      TRUNC(SYSDATE+1) + 16 \/24\r\n end\r\n into res\r\n from dual\r\n  ;\r\nreturn res;\r\n\r\nend;\r\n<\/pre>\n
once the function is created, all you need to do is use it in your dbms_job submit under the INTERVAL parameter<\/p>\n
\r\ndeclare\r\n  my_job number;\r\nbegin\r\n  dbms_job.submit(job => my_job, \r\n    what => 'my_procedure ;',\r\n                     \r\n    interval => 'get_next_run_date'\r\n    );\r\nend\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Oracle jobs are a simple way to schedule procedure and processes. However, sometimes the scheduling requirements can get really complicated. A very common request is to run a job on a specific hour on weekdays and not to run it on the weekend. This request can be easily delivered using the “WHAT” parameter of the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[94,93,92],"class_list":["post-324","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-dbms_job","tag-ora-06512","tag-ora-12899"],"_links":{"self":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/324","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=324"}],"version-history":[{"count":4,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/324\/revisions"}],"predecessor-version":[{"id":328,"href":"https:\/\/dbtricks.com\/index.php?rest_route=\/wp\/v2\/posts\/324\/revisions\/328"}],"wp:attachment":[{"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=324"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=324"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtricks.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=324"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}