| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: DBMS_JOB
Just found an online version (in the spirit of sharing solutions ;D):
http://otn.oracle.com/oramag/oracle/03-jan/o13asktom.html
---snip---
Setting a Complex Interval
I am using DBMS_JOB, and I want to schedule a job that runs every 15 minutes from Monday to Friday, between 6 a.m. and 6 p.m. How do I schedule it? I cannot figure out the interval I should be passing.
Well, for figuring out complex intervals for DBMS_JOB, I like to use the new
(as of Oracle8i Release 2) CASE statement. For example, the following CASE
statement returns the correct interval for your specification:
SQL> alter session set nls_date_format =
2 'dy mon dd, yyyy hh24:mi';
Session altered.
SQL> select
2 sysdate,
3 case
4 when (to_char( sysdate, 'hh24' )
5 between 6 and 17
6 and to_char(sysdate,'dy') NOT IN
7 ('sat','sun') )
8 then trunc(sysdate)+
9 (trunc(to_char(sysdate,'sssss')/
10 900)+1)*15/24/60
11 when (to_char( sysdate, 'dy' )
12 not in ('fri','sat','sun') )
13 then trunc(sysdate)+1+6/24
15 'Mon' )+6/24
SYSDATE
The CASE statement gives you great flexibility in generating a complex value such as you need. Unfortunately, DBMS_JOB will allow you to use only an interval that is 200 characters or less, and even if you "scrunch up" the CASE statement above, you'll find it is about 300 characters minimally. So, you cannot use it directly in the call to DBMS_JOB. My solution to that is one of two things: either I would create a view NEXT_DATE as that select, so that select * from next_date would return the next time the job runs, or I would wrap the above query in a PL/SQL function that returns a date. If I used a view, my call to DBMS_JOB might look like:
begin
dbms_job.submit
( :n, 'proc;', sysdate,
'(select * from next_date)'
);
end;
/
Or, if I used the PL/SQL function approach and created a function NEXT_DATE, it could be:
begin
dbms_job.submit
( :n, 'proc;', sysdate,
'next_date()'
);
end;
/
---snip---
Cheers
Mark
-----Original Message-----
Sent: 18 February 2003 15:29
To: Multiple recipients of list ORACLE-L
DOH! You know, I knew that I had read about this somewhere, but couldn't for the life of me remember where. You mentioning Tom kick started my aching brain! ;)
The asktom site is down at the moment whilst being upgraded, but I still have the copy of Oracle magazine where Tom discussed this! For all others that may be interested, page 98 of Oracle Magazine (January/February 2003 edition) discusses this ("Setting a Complex Interval")!
Thanks Kirti!
Mark
-----Original Message-----
Sent: 18 February 2003 15:09
To: ORACLE-L_at_fatcity.com
Cc: mark_at_cool-tools.co.uk
Mark,
You may want to check http://asktom.oracle.com.
I think there are a few examples that you will find come close to what you
are looking for.
-----Original Message-----
Sent: Tuesday, February 18, 2003 8:14 AM
To: Multiple recipients of list ORACLE-L
Hi All,
Does anybody know a way of to get DBMS_JOB to run a job every 15 minutes, unless the time of day is between X and Y (for example 00:00am > 03:00am)?
All help appreciated! Pointers to RTFM more than welcome! ;)
Mark
Mark Leith | T: +44 (0)1905 330 281
Sales & Marketing | F: +44 (0)870 127 5283
Cool Tools UK Ltd | E: mark_at_cool-tools.co.uk
===================================================
http://www.cool-tools.co.uk
Maximising throughput & performance
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: mark_at_cool-tools.co.uk Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Tue Feb 18 2003 - 09:54:14 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: mark_at_cool-tools.co.uk Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Leith INET: mark_at_cool-tools.co.uk Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |