A Simple Example
Suppose that I am working with a banking application and want to schedule the execution of a job that applies interest daily to the account balances. A stored procedure called apply_interest is used to apply the interest. I can use DBMS_SCHEDULER’s CREATE_JOB program to create a scheduler job named “apply_daily_interest” as follows:
1 BEGIN
2 DBMS_SCHEDULER.create_job (job_name => 'apply_daily_interest',
3 job_type => 'STORED_PROCEDURE',
4 job_action => 'apply_interest',
5 repeat_interval => 'FREQ=DAILY; INTERVAL=1',
6 enabled => TRUE,
7 comments => 'Apply Daily Interest'
8 );
9* END;Let’s examine the lines in detail in the following table.
|
Line |
Description |
|
2 |
I define the name of the job. The name must be a valid Oracle identifier, which means that it must start with a letter; contain some combination of letters, numbers, #, _, or $; and cannot be longer than 30 characters. The name also must be unique in the Oracle namespace; it cannot, for example, have the same name as a table. |
|
3 |
Here I specify what type of program is to be run. This is a wonderful enhancement of DBMS_SCHEDULER over the functionality available in DBMS_JOB. In this case, the program to be executed (apply_interest) is a stored procedure, indicated by the value (“STORED_PROCEDURE”) of the job_type parameter. Other valid values for this argument are “PLSQL_BLOCK” and “EXECUTABLE”. |
|
4 |
The job_action parameter indicates what this job does—in this case, it executes the stored procedure apply_interest. ... |