RE: DBMS_JOB scheduling

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 23 Jul 2003 10:16:43 -0400
Message-Id: <25998.339124@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------=_NextPartTM-000-735b1e1b-1699-449f-9228-46031666edfe Content-Type: multipart/alternative;

boundary="----_=_NextPart_001_01C35125.0AB9F3B7"

------_=_NextPart_001_01C35125.0AB9F3B7
Content-Type: text/plain;

charset="iso-8859-1"

Igor,

you are right ... as a unwritten policy we don't allow windows databases ... even for crash test dummies ...

Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
From: Igor Neyman [mailto:ineyman_at_perceptron.com] Sent: Wednesday, July 23, 2003 10:40 AM
To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB scheduling

Raj,

You must be speaking from "UNIX heights" -:-)

Under Windows I find dbms_job much more reliable than windows "at" scheduling.

Actually, never had problems with dbms_job "forgetting" to run a job.

Igor Neyman, OCP DBA

ineyman_at_perceptron.com

-----Original Message-----
From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On Behalf Of Jamadagni, Rajendra
Sent: Wednesday, July 23, 2003 9:24 AM
To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB scheduling

Garry,

have you tried select to_char(sysdate,'D') from dual ??

This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number.

nevertheless, I think what you have attempted is fantastic and worthy of adoption ...

Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
From: Garry Gillies [ mailto:g.gillies_at_weir.co.uk
<mailto:g.gillies_at_weir.co.uk> ]

Sent: Wednesday, July 23, 2003 5:59 AM
To: Multiple recipients of list ORACLE-L Subject: DBMS_JOB scheduling

Any Interest?

The DBMS_JOB package is supplied by Oracle to allow the running of procedures at regular intervals. Unfortunately the INTERVAL parameter is limited to 128

characters, which prevents you from getting very complex (user defined functions [in the interval parameter] do not work well - according to Fuerstein in his book Oracle Built In Packages).

The situation is eased somewhat by the fact that the NEXT_DATE parameter can be supplied to the procedure as an in/out parameter - and the procedure can contain whatever code is necessary to calculate when next to run. This is all very well, but custom coding scheduling routines can quickly become tedious.

On the basis of "do it once and get it over with" I have written a function called NEXT_DATE which I have wrapped in a package called CRON.

There is a Unix program called cron which runs jobs on a regular basis. Although the scheduling data supplied to cron is simple and concise, complex schedules are easy to specify.

The NEXT_DATE function takes in a cron schedule string and returns the next date that conforms to the schedule - or you can supply a cron schedule and a date

and it will return the first date after the supplied date that conforms to the schedule. At the moment it is not very friendly on the error detection front. A VALUE_ERROR is returned if it deems the cron schedule to be invalid. You will also get a VALUE_ERROR if the next valid date is more than twenty seven years in the future. DBMS_OUTPUT is used to display error messages which will hopefully give you a clue.

This will be improved if I receive enough complaints ( and suggestions for improvements).

THE CRON SCHEDULE A cron schedule consists of five components, each separated from the next by a space.
The syntax is identical for all components. The components represent

         Minute in Hour 
         Hour in day 
         Day in month 
         Month in year 
         Day of Week - A bit of a bugger this one. 
         In Unix land the day numbering runs from 0-6 with 0 being Sunday.

In Oracle the day numbering depends on the setting of NLS_TERRITORY.

I have chosen to go with ISO standard 8601:1998 which runs from 1-7 with 1 being Monday. This is so close to the Unix convention that I can interpret Unix cron schedules correctly.

Curiously, Oracle do not provide a date format which supplies this number. The ISO week number is available with the format 'IW', but not the ISO day number. If you have a field of type date called dt, you can obtain the ISO day number with ( trunc(dt) - trunc(dt ,'IW') ) + 1

A component can consist of an asterisk * which represents all valid values or a number of elements separated by a comma (if only one element is

supplied, forget the comma). An element can be a single number - valid for the component (32 in "Day in month" is invalid) or two numbers separated by a hyphen - which represents a range.

EXAMPLES Run every hour on the hour

0 * * * *
Run twice every hour, on the hour and on the half hour

0,30 * * * *
Run twice every hour, on the hour and on the half hour between 08:00 and 16:59

0,30 8-16 * * *
Run twice every hour, on the hour and on the half hour between 08:00 and 16:59, Monday to Friday

0,30 8-16 * * 1-5
Run at 11:12 every Friday the 13th

11 12 13 * 5
Run at 04:00 every leap year on february 29

0 4 29 2 *
Run at 04:00 every leap year on february 29 when february 29 is a Thursday

0 4 29 2 4

Garry Gillies
Database Administrator
Business Systems
Weir Pumps Ltd
149 Newlands Road, Cathcart, Glasgow, G44 4EX

T: +44 0141 308 3982 
F: +44 0141 633 1147 
E: g.gillies_at_weirpumps.com

------_=_NextPart_001_01C35125.0AB9F3B7
Content-Type: text/html;

charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>RE: DBMS_JOB scheduling</TITLE>

<META content="MSHTML 5.50.4926.2500" name=GENERATOR>
<STYLE>
<!--

/* Font Definitions */
@font-face

	{font-family:Wingdings;
	panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}

/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal

	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:blue;
	text-decoration:underline;}
p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:10.0pt;
	font-family:"Courier New";}
p
	{margin-right:0in;
	margin-left:0in;
	font-size:12.0pt;
	font-family:"Times New Roman";}
span.EmailStyle18
	{font-family:Arial;
	color:navy;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}

-->
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=blue link=blue>
<DIV>Igor,</DIV>
<DIV> </DIV>
<DIV>you are right ... as a unwritten policy we don't allow windows databases ... even for crash test dummies ...</DIV>
<DIV> </DIV>
<DIV>Raj</DIV>
<DIV><FONT face="Courier New"

size=2>--------------------------------------------------------------------------------</FONT>

 Rajendra dot Jamadagni at nospamespn dot
com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
 <DIV class=OutlookMessageHeader dir=ltr align=left>-----Original Message----- From: Igor Neyman [mailto:ineyman_at_perceptron.com] Sent: Wednesday, July 23, 2003 10:40 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB scheduling </DIV> <DIV class=Section1>
 Raj,   You must be speaking from "UNIX heights" -J Under Windows I find dbms_job much more reliable than windows "at" scheduling. Actually, never had problems with dbms_job "forgetting" to run a job.   <DIV>
 Igor Neyman, OCP DBA ineyman_at_perceptron.com  </DIV>   -----Original Message----- From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com] On Behalf Of Jamadagni, Rajendra Sent: Wednesday, July 23, 2003 9:24 AM To: Multiple recipients of list ORACLE-L Subject: RE: DBMS_JOB scheduling

  <P style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman" size=2><SPAN 
  style="FONT-SIZE: 10pt">Garry,</SPAN></FONT> </P>
  <P style="MARGIN-LEFT: 0.5in"><FONT face="Times New Roman" size=2><SPAN

style="FONT-SIZE: 10pt">1. have you tried select to_char(sysdate,'D') from dual ?? 
 This is really nice, but my only gripe with dbms-job is that is isn't reliable ... it wasn't in 9ir1 on aix and we didn't even look at it in 9ir2. in 9ir1 dbms_job used to _forget_ to run jobs after some time and the workaround was like setting job_processes to a very large number.
 nevertheless, I think what you have attempted is fantastic and worthy of adoption ... Raj <SPAN

  style="FONT-SIZE: 10pt">--------------------------------------------------------------------------------</SPAN></FONT>

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !   -----Original Message----- From: Garry Gillies [<A href="mailto:g.gillies_at_weir.co.uk">mailto:g.gillies_at_weir.co.uk</A>] Sent: Wednesday, July 23, 2003 5:59 AM To: Multiple recipients of list ORACLE-L Subject: DBMS_JOB scheduling   Any Interest? The DBMS_JOB package is supplied by Oracle to allow the running of procedures at regular intervals. Unfortunately the INTERVAL parameter is limited to 128 characters, which prevents you from getting very complex (user defined functions [in the interval parameter] do not work well - according to Fuerstein in his book Oracle Built In Packages).
 The situation is eased somewhat by the fact that the NEXT_DATE parameter can be supplied to the procedure as an in/out parameter - and the procedure can contain whatever code is necessary to calculate when next to run. This is all very well, but custom coding scheduling routines can quickly become tedious. On the basis of  "do it once and get it over with" I have written a function called NEXT_DATE which I have wrapped in a package called CRON. There is a Unix program called cron which runs jobs on a regular basis. Although the scheduling data supplied to cron is simple and concise,  complex schedules are easy to specify. The NEXT_DATE function takes in a cron schedule string and returns the next date that conforms to the schedule - or you can supply a cron schedule and a date and it will return the first date after the supplied date that conforms to the schedule. At the moment it is not very friendly on the error detection front. A  VALUE_ERROR is returned if it deems the cron schedule to be invalid. You will also get a  VALUE_ERROR if the next valid date is more than twenty seven years in the future. DBMS_OUTPUT is used to display error messages which will hopefully give  you a clue. 
 This will be improved if I receive enough complaints ( and suggestions for  improvements). THE CRON SCHEDULE A cron schedule consists of five components, each separated from the next by a space. The syntax is identical for all components. The components represent          Minute in Hour          Hour in day          Day in month          Month in year          Day of Week - A bit of a bugger this one.          In Unix land the day numbering runs from 0-6 with 0 being Sunday. In Oracle the day numbering depends on the setting of NLS_TERRITORY.          I have chosen to go with ISO standard  8601:1998 which runs from 1-7 with 1 being Monday. This is so close to the Unix convention that I can interpret Unix cron schedules correctly.          Curiously, Oracle do not provide a date format which supplies this number. The ISO week number is available with the format 'IW', but not the ISO day number. If you have a field of type date called dt, you can obtain the ISO day number with ( trunc(dt) - trunc(dt ,'IW') ) + 1 A component can consist of an asterisk   *    which represents all valid values or a number of elements separated by a comma (if only one element is supplied, forget the comma). An element can be a single number - valid for the component (32 in "Day in month" is invalid) or two numbers separated by a hyphen   -   which represents a range.
 EXAMPLES Run every hour on the hour      0 * * * * Run twice every hour, on the hour and on the half hour      0,30 * * * * Run twice every hour, on the hour and on the half hour between 08:00 and 16:59      0,30 8-16 * * * Run twice every hour, on the hour and on the half hour between 08:00 and 16:59, Monday to Friday     0,30 8-16 * * 1-5 Run at 11:12 every Friday the 13th     11 12 13 * 5 Run at 04:00 every leap year on february 29     0 4 29 2 * Run at 04:00 every leap year on february 29 when february 29 is a Thursday     0 4 29 2 4 Garry Gillies Database Administrator <FONT

  size=2><SPAN style="FONT-SIZE: 10pt">Business Systems</SPAN></FONT> <BR><FONT 
  size=2><SPAN style="FONT-SIZE: 10pt">Weir Pumps Ltd</SPAN></FONT> <BR><FONT 
  size=2><SPAN style="FONT-SIZE: 10pt">149 Newlands Road, Cathcart, Glasgow, G44

4EX T: +44 0141 308 3982 F: +44 0141 633 1147 E: g.gillies_at_weirpumps.com </DIV></BLOCKQUOTE></BODY></HTML>

------_=_NextPart_001_01C35125.0AB9F3B7--

------=_NextPartTM-000-735b1e1b-1699-449f-9228-46031666edfe Content-Type: text/plain;

name="ESPN_Disclaimer.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;

filename="ESPN_Disclaimer.txt"

********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************2

Received on Wed Jul 23 2003 - 09:16:43 CDT