Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Calendar Syntax in Oracle Job Scheduling
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Oracle 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 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 semicolon.

  • 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 countback.  As such, bymonthday=-1 equates to the last day of the month. The documentation states that countbacks 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 the 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.

 

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.