{"id":158,"date":"2017-03-22T12:47:00","date_gmt":"2017-03-22T16:47:00","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2017\/03\/22\/scheduling-jobs-with-oracle-9i-dbms_job-package\/"},"modified":"2017-03-22T12:47:00","modified_gmt":"2017-03-22T16:47:00","slug":"scheduling-jobs-with-oracle-9i-dbms_job-package","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2017\/03\/22\/scheduling-jobs-with-oracle-9i-dbms_job-package\/","title":{"rendered":"Scheduling Jobs with Oracle 9i DBMS_JOB Package"},"content":{"rendered":"<p>Creating a new job that runs every day at 4:00am:<\/p>\n<pre class=\"code\">\ndeclare \n\tl_job number; \nbegin \n\tdbms_job.submit(\n\t\tl_job, -- OUT; the job ID number that will be generated\n\t\t'schema_name.procedure_name;', -- IN; the name of the job you wish to run, aka. \"what\"\n\t\ttrunc(sysdate)+1+4\/24,  -- IN; the first time the job will be run\n\t\t'trunc(sysdate)+1+4\/24' -- IN; the interval the job will be repeated\n\t); \nend; \n<\/pre>\n<p>Regarding the interval, here are some examples:<\/p>\n<pre class=\"code\">\n-- Every 15 minutes starting from the minute\/second of the previous execution\n'sysdate+1\/24\/4'\n\n-- Every hour, same minute\/second as the previous execution\n'sysdate+1\/24'\n\n-- Every hour, at the 15-minute mark\n'trunc(sysdate, 'hh')+1\/24+15\/24\/60'\n\n-- Every hour, limited to between 9:00am and 5:00pm\n'case when to_char(sysdate, ''hh24mi'') between ''0900'' and ''1700'' then sysdate+1\/24 else null end'\n\n-- Every 3 days, same hour\/minute\/second as the previous execution\n'sysdate+3'\n\n-- Every day at 5:00am\n'trunc(sysdate)+1+5\/24'\n\n-- Every Monday at 5:00am\n'next_day(trunc(sysdate), ''monday'')+5\/24'\n<\/pre>\n<p>To see a list of existing jobs:<\/p>\n<pre class=\"code\">\nselect * from dba_jobs;\n<\/pre>\n<p>Altering all properties of an existing job:<\/p>\n<pre class=\"code\">\nbegin\n\tdbms_job.change(\n\t\t123, -- IN; job ID number\n\t\t'schema_name.procedure_name;', -- IN; the name of the job, aka. \"what\"\n\t\ttrunc(sysdate)+1+4\/24,  -- IN; the first time the job will be run after this change\n\t\t'trunc(sysdate)+1+4\/24' -- IN; the interval the job will be repeated\n\t);\nend;\n<\/pre>\n<p>Altering just the &#8220;what&#8221;:<\/p>\n<pre class=\"code\">\nbegin\n\tdbms_job.what(\n\t\t123, -- IN; job ID number\n\t\t'schema_name.procedure_name;' -- IN; the name of the job, aka. \"what\"\n\t);\nend;\n<\/pre>\n<p>These procedures allows you to make changes in a manner very similar to dbms_job.what illustrated above:<\/p>\n<pre class=\"code\">\n\t.next_date\n\t.interval\n<\/pre>\n<p>Force a job to run:<\/p>\n<pre class=\"code\">\nbegin\n\tdbms_job.run(123);\n\t-- ... where the \"123\" is the job's ID number\nend;\n<\/pre>\n<p>Removing an existing job:<\/p>\n<pre class=\"code\">\nbegin\n\tdbms_job.remove(123);\n\t-- ... where the \"123\" is the job's ID number\nend;\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>This article illustrates the usage of Oracle 9i&#8217;s DBMS_JOB package: adding, altering, removing, etc. of jobs.  It also provides a small set of examples of time intervals, useful for setting up job schedules.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-158","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/158","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=158"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/158\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}