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:
- Create a schedule
- Identify a ‘program’ – by which they mean the procedure you wish to run
- 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.
|Schedule_Name||VARCHAR2||A unique name for your schedule|
|Repeat_interval||VARCHAR2||Describes the frequency using the calendaring syntax.|
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.
|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.
|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.');
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!