Introduction to Scheduled Jobs

Psst, 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 ten quid. It is a truth that life’s events – and, by extension, computer operations – are… Continue Reading →

Psst, 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 ten quid.

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.

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. (More on DBMS_JOB later.)

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. The TV show Lost once expended a number of episodes on a storyline that involved characters having to frantically type a chain of numbers – parameters – into a computer every 98 minutes to prevent a bomb from destroying their island. Had Lost been sponsored by Oracle there would have been a close-up of Desmond calling the DBMS_SCHEDULER package and setting the frequency of his schedule. And that’s it; world saved.

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 which is a bit like the cron 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. If, for instance, you were Desmond from Lost you would – to a background of tense cliffhanger music – 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 improbable 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 executables 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 and use that time watching characters type mindlessly in Lost!