Oracle PL/SQL for DBAs

Calendar Strings

The syntax of the calendar string you can specify in CREATE_JOB’s repeat_interval parameter is quite simple and English-like. For example, if you want to schedule a job that runs Monday through Friday at exactly 7 A.M. and 3 P.M., you would specify the following calendar string as the value for repeat_interval:

    FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=7,15

The calendar string has two distinct types of clauses, FREQ and BY..., separated by semicolons. You specify only one FREQ clause , but you may specify several different types of BY clauses, depending on your calendar. Each clause consists of a keyword and a value, separated by an equal sign. Together, the clauses specify how often a job is expected to run, as follows:

Unit of frequency (FREQ)

The FREQ clause specifies the repetition unit. In the example, I want the job to be repeated every day, so I specify FREQ = DAILY. Valid keywords are YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.

Limits (BY...)

The BY clause limits exactly when the job will execute. In the example, I start by specifying the unit of frequency as every day (FREQ=DAILY). I then limit execution to only specific days (Monday, Tuesday, Wednesday, Thursday, and Friday), by specifying BYDAY=MON,TUE,WED,THU,FRI. I then further limit execution by requesting that the job be run at 7 A.M. and 3 P.M., by specifying BYHOUR=7,15. Valid keywords are BYMONTH, BYMONTHDAY, BYYEARDAY, BYHOUR, BYMINUTE, and BYSECOND. These are described ...