Oracle PL/SQL for DBAs

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. ...