Oracle-L: RE: DBMS

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Tue, 18 Feb 2003 07:54:14 -0800
Message-ID: <F001.00550468.20030218075414@fatcity.com>

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
14 else next_day( trunc(sysdate),

 15                   'Mon' )+6/24

16 end interval_date
17 from dual
18 /

SYSDATE

INTERVAL_DATE

sun sep 15, 2002 16:35
mon sep 16, 2002 06:00

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.

Kirti

-----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-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).

-- 
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).

Received on Tue Feb 18 2003 - 09:54:14 CST

RE: DBMS_JOB