Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle 10g Scheduling Calendar Syntax

Oracle Tips by Burleson Consulting

Calendar Syntax in Oracle10g

Oracle10g introduced a calendar syntax allowing complex job execution cycles to be defined in a simple and clear manner.  The calendar syntax is listed below:

repeat_interval = freq=?
  [; interval=?] [; bymonth=?] [; byweekno=?]
  [; byyearday=?] [; bymonthday=?] [; byday=?]
  [; byhour=?] [; byminute=?] [; bysecond=?]

Before investigating what the individual clauses of this syntax mean, how the calendar strings can be tested should be explained.  The evaluate_calendar_string procedure from the dbms_scheduler package returns run timestamps by evaluating a specified calendar string.

PROCEDURE evaluate_calendar_string (
   calendar_string    IN  VACRHAR2,
   start_date         IN  TIMESTAMP WITH TIME ZONE,
   return_date_after  IN  TIMESTAMP WITH TIME ZONE,
   next_run_date      OUT TIMESTAMP WITH TIME ZONE);

The parameters associated with this procedure and their usage are as follows:

* calendar_string - The calendar string to be evaluated.

* start_date - The date the calendar string becomes valid.  If elements of the calendar string are missing, they may be derived from elements of this date.

* return_after_date - Only dates after this date will be returned by the procedure.  If no date is specified, the current systimestamp is used.

* next_run_date - The first date that matches the calendar_string and start_date and is greater than the run_after_date.

The test_calendar_string.sql procedure, listed below, uses the evaluate_calendar_string procedure to display a list of run dates.  For convenience, the start_date and run_after_date parameters are defaulted.

*
test_calendar_string.sql

set serveroutput on;
alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';

CREATE OR REPLACE PROCEDURE test_calendar_string(
  p_calendar_string  IN  VARCHAR2,
  p_iterations       IN  NUMBER DEFAULT 5)
AS
  l_start_date         TIMESTAMP := TO_TIMESTAMP('01-JAN-2004 03:04:32',
                                               'DD-MON-YYYY HH24:MI:SS');
  l_return_date_after  TIMESTAMP := l_start_date;
  l_next_run_date      TIMESTAMP;
BEGIN
  FOR i IN 1 .. p_iterations LOOP
    DBMS_SCHEDULER.evaluate_calendar_string ( 
      calendar_string   => p_calendar_string,
      start_date        => l_start_date,
      return_date_after => l_return_date_after,
      next_run_date     => l_next_run_date);

    DBMS_OUTPUT.put_line('Next Run Date: ' || l_next_run_date);
    l_return_date_after := l_next_run_date;
  END LOOP;
END;
/

The individual clauses that make up a calendar string are explained in Table 3.10 below.  The test_calendar_string procedure is used to display sample run timestamps to make the element usage clear.

PARAMETER

MEANING

Freq

Specifies the frequency, or type of recurrence, and is the only mandatory clause.  Its allowable values are: yearly, monthly, weekly, daily, hourly, minutely and secondly.

SQL> -- Run every second.
SQL> exec test_calendar_string('freq=secondly');
Next Run Date: 01-JAN-2004 03:04:33
Next Run Date: 01-JAN-2004 03:04:34
Next Run Date: 01-JAN-2004 03:04:35
Next Run Date: 01-JAN-2004 03:04:36
Next Run Date: 01-JAN-2004 03:04:37

PL/SQL procedure successfully completed.

interval

Specifies the interval associated with the specified frequency, such that an interval of 2 on a minutely frequency would cause the job to execute every other minute, while an interval of 10 would cause it to execute every 10 minutes.  The allowable values are 1 to 999, with a default value of 1.

SQL> -- Run every 30 minutes
SQL> exec test_calendar_string('freq=minutely; interval=30');
Next Run Date: 01-JAN-2004 03:34:32
Next Run Date: 01-JAN-2004 04:04:32
Next Run Date: 01-JAN-2004 04:34:32
Next Run Date: 01-JAN-2004 05:04:32
Next Run Date: 01-JAN-2004 05:34:32

PL/SQL procedure successfully completed.

bymonth

Specifies the month or months on which the job should execute. The month can be specified using the month number (1-12) or using the three letter abbreviation (jan-dec).

SQL> -- Run on months 1, 2, 11 and 12.
SQL> exec test_calendar_string('freq=monthly; bymonth=1,2,11,12');
Next Run Date: 01-FEB-2004 03:04:32
Next Run Date: 01-NOV-2004 03:04:32
Next Run Date: 01-DEC-2004 03:04:32
Next Run Date: 01-JAN-2005 03:04:32
Next Run Date: 01-FEB-2005 03:04:32

PL/SQL procedure successfully completed.

SQL> -- Run on months JAN, FEB, NOV and DEC.
SQL> exec test_calendar_string('freq=monthly; bymonth=jan,feb,nov,dec');
Next Run Date: 01-FEB-2004 03:04:32
Next Run Date: 01-NOV-2004 03:04:32
Next Run Date: 01-DEC-2004 03:04:32
Next Run Date: 01-JAN-2005 03:04:32
Next Run Date: 01-FEB-2005 03:04:32

PL/SQL procedure successfully completed.

byweekno

Specifies the week or weeks of the year to include for strings with a yearly frequency.  Allowable values are one to 52 (or 53), with the week starting on a Monday and finishing on a Sunday.

SQL> -- Run on the first Monday of weeks 10,20,30,40,50.
SQL> exec test_calendar_string('freq=yearly; byweekno=10,20,30,40,50; byday=mon');
Next Run Date: 01-MAR-2004 03:04:32
Next Run Date: 10-MAY-2004 03:04:32
Next Run Date: 19-JUL-2004 03:04:32
Next Run Date: 27-SEP-2004 03:04:32
Next Run Date: 06-DEC-2004 03:04:32

PL/SQL procedure successfully completed.

byyearday

Specifies the day or days of the year with valid values from one to 366.

SQL> -- Run on the 100th, 200th and 300th days of the year.
SQL> exec test_calendar_string('freq=yearly; byyearday=100,200,300');
Next Run Date: 09-APR-2004 03:04:32
Next Run Date: 18-JUL-2004 03:04:32
Next Run Date: 26-OCT-2004 03:04:32
Next Run Date: 10-APR-2005 03:04:32
Next Run Date: 19-JUL-2005 03:04:32

PL/SQL procedure successfully completed.

bymonthday

Specifies the day or days of the month with valid values from one to 31.  Using a negative number results in a count backwards from the end of the month such that   -1 means the last day of the month.

SQL> -- Run on the first two days of the month.
SQL> exec test_calendar_string('freq=monthly; bymonthday=1,2');
Next Run Date: 02-JAN-2004 03:04:32
Next Run Date: 01-FEB-2004 03:04:32
Next Run Date: 02-FEB-2004 03:04:32
Next Run Date: 01-MAR-2004 03:04:32
Next Run Date: 02-MAR-2004 03:04:32

PL/SQL procedure successfully completed.

SQL> -- Run on the last two days of the month.
SQL> exec test_calendar_string('freq=monthly; bymonthday=-1,-2');
Next Run Date: 30-JAN-2004 03:04:32
Next Run Date: 31-JAN-2004 03:04:32
Next Run Date: 28-FEB-2004 03:04:32
Next Run Date: 29-FEB-2004 03:04:32
Next Run Date: 30-MAR-2004 03:04:32

PL/SQL procedure successfully completed.

byday

Specifies the day or days of the week with reference to the frequency using the three letter abbreviation. When used with a yearly or monthly frequency, the day can be prefixed with a number.  Prefixing the day with a number indicates the occurrence of that number within the frequency, with negative numbers providing a backwards count.

SQL> -- Run on the thirty fifth Monday of the year.
SQL> exec test_calendar_string('freq=yearly; byday=35MON');
Next Run Date: 30-AUG-2004 03:04:32
Next Run Date: 29-AUG-2005 03:04:32
Next Run Date: 28-AUG-2006 03:04:32
Next Run Date: 27-AUG-2007 03:04:32
Next Run Date: 01-SEP-2008 03:04:32

PL/SQL procedure successfully completed.

SQL> -- Run on the second Monday of the month.
SQL> exec test_calendar_string('freq=monthly; byday=2MON');
Next Run Date: 12-JAN-2004 03:04:32
Next Run Date: 09-FEB-2004 03:04:32
Next Run Date: 08-MAR-2004 03:04:32
Next Run Date: 12-APR-2004 03:04:32
Next Run Date: 10-MAY-2004 03:04:32

PL/SQL procedure successfully completed.

SQL> -- Run on the last Wednesday of the month.
SQL> exec test_calendar_string('freq=monthly; byday=-1WED');
Next Run Date: 28-JAN-2004 03:04:32
Next Run Date: 25-FEB-2004 03:04:32
Next Run Date: 31-MAR-2004 03:04:32
Next Run Date: 28-APR-2004 03:04:32
Next Run Date: 26-MAY-2004 03:04:32

PL/SQL procedure successfully completed.

byhour

Specifies the hour or hours of the day with valid values from zero to 23.  Negative numbers result in a backwards count.

-- Run on the first hour of every day.
SQL> exec test_calendar_string('freq=daily; byhour=1');
Next Run Date: 02-JAN-2004 01:04:32
Next Run Date: 03-JAN-2004 01:04:32
Next Run Date: 04-JAN-2004 01:04:32
Next Run Date: 05-JAN-2004 01:04:32
Next Run Date: 06-JAN-2004 01:04:32

PL/SQL procedure successfully completed.

byminute

Specifies the minute or minutes of the hour with valid values from zero to 59.

-- Run on the first minute of every hour.
SQL> exec test_calendar_string('freq=hourly; byminute=1');
Next Run Date: 01-JAN-2004 04:01:32
Next Run Date: 01-JAN-2004 05:01:32
Next Run Date: 01-JAN-2004 06:01:32
Next Run Date: 01-JAN-2004 07:01:32
Next Run Date: 01-JAN-2004 08:01:32

PL/SQL procedure successfully completed.

bysecond

Specifies the second or seconds of the minute with valid values from zero to 59.  Negative numbers result in a backwards count.

-- Run on the first second of every minute.
SQL> exec test_calendar_string('freq=minutely; bysecond=1');
Next Run Date: 01-JAN-2004 03:05:01
Next Run Date: 01-JAN-2004 03:06:01
Next Run Date: 01-JAN-2004 03:07:01
Next Run Date: 01-JAN-2004 03:08:01
Next Run Date: 01-JAN-2004 03:09:01

PL/SQL procedure successfully completed.

Table 3.10 ? Calendar parameters and their meanings

The following points contain general guidance information for the use of calendar syntax during scheduling:

* The calendar string must contain a frequency as the first clause.  All other clauses are optional and can be placed in any order.

* Each clause can only be present once and must be separated by a semi-colon.

* The calendar strings are not case sensitive and white spaces between clauses are allowed.

* Where a BY clause contains a list of values, the order of the list is not important.

* When there are not enough clauses to determine the precise run date, the missing clauses are derived from the start_date.  For example, if there is no bysecond clause in the calendar string, the value of seconds from the start_date is used to create one.

* When a number range is not fixed, the last value of the range can be determined using a negative integer as a count-back.  As such bymonthday=-1 equates to the last day of the month. The documentation states that count-backs are not supported for fixed number ranges such as those used by the bymonth, byhour, byminute and bysecond clauses, but they do appear to work consistently.

* The first day of the week is Monday.

* A calendar string cannot specify time zones.  Instead the time zone is derived from one of the following places in this order: the start_date, the current session?s time zone, the DEFAULT_TIMEZONE scheduler attribute, or time zone returned by the systimestamp function.

Now that calendar syntax has been introduced in detail, the following section will compare the use of PL/SQL expressions and the use of calendar syntax for scheduling jobs.

 

This is an excerpt from the book "Oracle Job Scheduling" by Dr. Tim Hall.

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle job scheduling scripts.


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational