For scheduling any Action in the database we need to create the following tasks
- Create Scheduling Object
- Create Program Object
- Create Job Object
Let us go in detail about each task
Creating Scheduling Object
The schedule determines the time interval when the action to occur in the database. While creating Schedule we need to pass the schedule name, start date, repeat interval, comments as parameters. The sample code is as shown.BEGIN DBMS_SCHEDULER.CREATE_SCHEDULE ( Schedule_name => ‘MP_JOB_SCHEDULE’ , Start_date => SYSTIMESTAMP , Repeat_interval =>’FREQ=MINUTELY;INTERVAL=15′ , Comments => ‘Hourly MP schema Job postings refresh’ ); END;
- Here the schedule_name us User defined.
- Start_date can have Current SYSDATE (or) any Future date.
- Repeat_interval can have values as MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY & SECONDLY
Here are some examples of using PL/SQL expressions:
REPEAT_INTERVAL=> `SYSDATE –1`
REPERT_INTERVAL=> `SYSDATE + 36/24`
- The comments are User defined.
To drop the schedule follows the same codeBEGIN DBMS_SCHEDULER.DROP_SCHEDULE( schedule_name => ‘MP_JOB_SCHEDULE’ ,force => TRUE ); END;
Creating Program Object
This object contains the main action logic that will occur when the scheduler is executed by the database. To create the program object we need to pass Program type, program action, enable, comments as parameters. The sample code is as followsBEGIN — Create a new MP_JOB_PROGRAM in disabled status DBMS_SCHEDULER.CREATE_PROGRAM ( program_name => ‘MP_JOB_PROGRAM’ ,program_type => ‘STORED_PROCEDURE’ ,program_action => ‘MP_JOBS_EMAIL.MP_SENT_MAIL’ ,number_of_arguments =>0 ,enabled => FALSE ,comments => ‘Refreshes all the Job Posting records in the MP schema’ ); END;
- Here the program_name is User define
- Program type can take values like STORED_PROCEDURE, PLSQL_BLOCK & EXECUTABLES.
- Program action will contain the Procedure (or) package name of particular Program type.
- If any arguments need to pass to the program, it is mentioned in the number_of_arguments parameter.
- Comments are User defined.
Once after creating the Program, One should not forgot to enable it. Using following the sample code as can do thisBEGIN DBMS_SCHEDULER.ENABLE ( name => ‘MP_JOB_PROGRAM’ ); END;
Now the Scheduler & Program are Ready to use with in a Job object. the creation of Job object is as follows.
Create Job Object
The job object will call a particular program in a specified schedule with in the dtabase.The job object need to have job name,program name,schedule name,enabled,comments as parameters.This can be clear by observing the following sample code.
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => ‘MP_JOB_POSTING’ ,program_name => ‘MP_JOB_PROGRAM’ ,schedule_name => ‘MP_JOB_SCHEDULE’ ,enabled => FALSE ,comments => ‘Refreshes the Job Postings in the MP Schema for every hour’ ); END;
- Here the job_name is User defined
- Program_name is the Specific program to be called by the job object.
- Schedule_name is the Specific schedule to be called by the job object.
- Enabled is to mention whether the job object is in Enabled (or) Disabled status.
- Comments are User Defined.
The job object can be bropped by using the following sample codeBEGIN DBMS_SCHEDULER.DROP_JOB ( job_name => ‘MP_JOB_POSTING’ ,force => TRUE ); END;
After creating the Schedule Object, Program Object & Job Object
We need to enable the Job object in order to make it run. Following the sample code can do thisBEGIN DBMS_SCHEDULER.ENABLE(‘MP_JOB_POSTING’); END;
Now the Job object will run in the database as per the specified time interval in the scheduler.
In the later stage if you want to change the parameter value of Schedule Object, Program Object (or) Job Object, this can be done by observing the following sample codeBEGIN DBMS_SCHEDULER.SET_ATTRIBUTE( name => ‘MP_JOB_SCHEDULE’ ,attribute => ‘start_date’ ,value => ’06/JUL/07 11:53:00 AM’ ); END;
Here only the name, attribute & value parameter values changes as per the object (Schedule, Program & Job).
To view the status of created objects in the database click here.