Introduction to Scheduled Jobs

 Wanna see something cool? I bet I can read your future. Your tomorrow is going to be almost exactly like your today; unless it’s a weekend, in which case it’ll be almost exactly the same as last weekend. You’re welcome: that’ll be 10K.

It is a truth that life’s events – and, by extension, computer operations – are mostly repetitive, iterative. Things happen, and then they happen again. Things happen, and then they happen again. Things happen, and then… you get the picture :P 

That is why we are rather lucky we have the DBMS_SCHEDULER package. It was introduced with Oracle 10g to supersede DBMS_JOB which, while still useful, was beginning to creak with age.

The DBMS_SCHEDULER package includes functionality that can be used to set up and manage the timetabling and execution of tasks that need to be run according to a – repeating or non-repeating – schedule. 

Roughly speaking, DBMS_SCHEDULER breaks the process of scheduling a task into 3 parts:

1. Create a schedule
2. Identify a ‘program’ – by which they mean the procedure you wish to run
3. Create a ‘job’ – by which they mean chain a program to a schedule.

The breaking up of the scheduling process into these 3 disparate actions is one of the main differences between DBMS_SCHEDULER and the old DBMS_JOB, and is what gives DBMS_SCHEDULER its flexibility and power, since a small number of schedules can be created and applied repeatedly to different programs. Let me explain:

Create A Schedule:

Schedules are created using the DBMS_SCHEDULER.CREATE_SCHEDULE procedure.

DBMS_SCHEDULER.CREATE_SCHEDULE (
	Schedule_Name	VARCHAR2	A unique name for your schedule
	Start_date	TIMESTAMP
	Repeat_interval	VARCHAR2	Describes the frequency using the calendaring syntax.
	End_date	TIMESTAMP
	Comments	VARCHAR2
);

At the very minimum, every schedule you create must have a name (a good practice would be to give it a descriptive name such as Sch_Monday_6am) and a repeat interval telling it when to execute. The repeat interval is very interesting; it uses a powerful calendaring syntax . It enables you quickly describe a wide range of schedules by simply defining a frequency, and one or more intervals.

The following are the predefined frequencies: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, SECONDLY.

The following are the predefined intervals:

INTERVAL	An integer between 1 and 99. Combined with a frequency (say DAILY or MINUTELY) it describes how regularly the schedule is executed
BYMONTH	The specified month for the task to be executed. It can be numbers (1 for January, 12 for December) or three-letter abbreviations (JAN for January)
BYWEEKNO	The number of the week of the year during which the schedule should be executed. (Can only be used in conjunction with the YEARLY frequency.) Weeks are deemed to start on Mondays. Valid values are integers between 1 and 52 or 53.
BYYEARDAY	The day of the year. Valid values are integers between 1 and 366.
BYDATE	A date in the following format: [YYYY]MMDD.
BYMONTHDAY	Day of the month expressed as an integer.
BYDAY	The day of the week expressed as a three letter abbreviation – MON, TUE, WED etc.
BYHOUR	An integer expressing the hour of the day. Valid values are 0 to 23.
BYMINUTE	An integer expressing the minute of the hour. Valid values are 0 to 59.
BYSECOND	An integer expressing the second of the minute. Valid values are 0 to 59.

By combining a frequency with one or more of the above intervals, you will quickly learn to describe any schedule. 

 – type the following:

Dbms_schedule.create_schedule
(schedule_name => 'SAVE_THE_WORLD',
Repeat_interval => 'FREQ=MINUTELY; INTERVAL=98',
Comments => 'Execute this task every 98 minutes because I am trapped in an TV show.');

If, in a less fanciful situation, you need to create a schedule that will execute at the close of work each day, you would type:

Dbms_schedule.create_schedule
(schedule_name => 'SCH_END_OF_WORK',
Repeat_interval => 'FREQ=DAILY;  BYDAY=MON, TUE, WED, THU, FRI; BYHOUR=17; BYMINUTE=30',
Comments => 'Run at 5.30pm every weekday.');

And, finally, if you need a schedule that will execute on the last day of each month:

Dbms_schedule.create_schedule
(schedule_name => 'SCH_LAST_DAY_OF_MONTH',
Repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=-1',
Comments => 'Since months are of varying lengths, minus one (the last day of the previous month) is the best way to do this.');

Create A Program:

Creating a program is a means of identifying the task that the job will execute according to the schedule. This is usually a stored procedure; it can be a standalone procedure, a package procedure or an anonymous block. However, programs can point at executable outside of the database; anything that can run from the command line.

DBMS_SCHEDULER.CREATE_PROGRAM (
	Program_name	VARCHAR2	A unique name for the program
	Program_type	VARCHAR2	The type of program being created. Valid options are ‘STORED_PROCEDURE’, ‘PLSQL_BLOCK’ and ‘EXECUTABLE’.
	Program_action	VARCHAR2	If the type is STORED_PROCEDURE, the action should be the name of the stored procedure. For PLSQL_BLOCK, it must be the full anonymous block and it must end with a semi-colon. For EXECUTABLE it must be the full path to the executable.
	Number_of_arguments	NUMBER	Number of parameters the action takes. This is ignored for PLSQL_BLOCK.
	Enabled	BOOLEAN	Should this program be created as enabled or not? The default is FALSE.
	Comments	VARCHAR2	Comments about the program
);

A simple example of a program is as follows:

Dbms_scheduler.create_program
(program_name => 'EXAMPLE_PROGRAM',
Program_type =>'STORED_PROCEDURE',
Program_action => 'example_pkg.example_procedure'
Enabled => TRUE,
Comments => 'This creates a program.');

Create A Job:

Schedules and programs are a little like men and women; on their own they are both pretty fine, but they need to get friendly if they wish to procreate. A program may identify a procedure but it will not run it; a schedule might create a timetable, but it does not define an action.

In DBMS_SCHEDULER, jobs are the means by which schedules and programs are wedded. However, the main advantage DBMS_SCHEDULER has over the old DBMS_JOB package is that a single schedule can be applied to multiple programs and a single program can be executed according to numerous schedules.

DBMS_SCHEDULER.CREATE_JOB(
	Job_name	VARCHAR2	A unique name for the job
	Program_name	VARCHAR2	The name of the program to be executed.
	Schedule_name	VARCHAR2	The name of the schedule.
	Enabled	BOOLEAN	Should the job be enabled? The default is False.
	Comments	VARCHAR2	A description of the job.
);

A simple example follows:

Dbms_scheduler.create_job
(Job_name => 'Example_job',
Program_name => 'Example_program',
Schedule_name => 'Sch_last_day_of_month',
Enabled => TRUE,
Comments => 'This job will run the example_program program according to the sch_last_day_of_month schedule.');

Conclusion:

DBMS_JOB allowed programmers to create, execute and schedule a job in a single command. However, by breaking that action down to its composite steps, DBMS_SCHEDULER is much more powerful. Add to this the fact that it introduces the simple yet powerful calendaring syntax that allows programmers to create complex schedules, and you have the making of very useful functionality. Now, instead of spending all our time typing into a computer we can schedule our tasks.