12.2 How do I…Submit scheduled jobs with DBMS_JOB?

Problem

In our application, I have some processes that need to be run regularly to update data and perform calculations. The operating system can schedule jobs, but we want our database applications to be operating system independent. How do I schedule programs within Oracle?

Technique

The DBMS_JOB package allows stored procedures to be run periodically, providing a means to manage background batch processes. The DBMS_JOB package contains procedures to control job scheduling. Table 12.1 lists the procedures embodied in the DBMS_JOB package.

Table 12.3 Procedures in the DBMS_JOB package

Procedure Description
ISUBMIT Submits a new job with a specified job number.
SUBMIT Submits a new job. Returns a unique job number.
REMOVE Removes an existing job from the job queue.
CHANGE Changes one or more job attributes.
WHAT Changes the program on which the job is to run.
NEXT_DATE Changes when an existing job is to be run.
INTERVAL Changes the interval that the job runs.
BROKEN Sets the broken flag. Terminates scheduling of a job.
RUN Executes a job immediately (even if it is broken).
USER_EXPORT Produces the text of a call to re-create the specified job.

The SUBMIT procedure is used to send a job to the queue along with parameters specifying how often it should run. Each job submitted to the queue is given a unique job number for identification. Alternatively, the ISUBMIT procedure is used to submit a job with a specified job number. To remove a job from the queue, use the REMOVE procedure, but a running job cannot be stopped with the REMOVE procedure.

The WHAT, NEXT_DATE, and INTERVAL procedures are used to alter a single characteristic of a job, while the CHANGE procedure allows changes to more than one job characteristic in a single call. The BROKEN procedure can be used to mark the job status as broken and the job will not be run again. You can use the RUN procedure in the DBMS_JOB package to force a job to run immediately (even a broken job). If the job completes successfully, the job status is marked as not broken. In order to mark the job as broken again, the BROKEN procedure has to be called explicitly. Jobs currently in the queue can be viewed by querying the USER_JOBS data dictionary view. Important columns of the USER_JOBS view are listed in Table 12.4.

Table 12.4 Important columns in the USER_OBJECTS view

Column Description
JOB Job number.
LOG_USER USER who submitted the job.
PRIV_USER USER whose privileges apply to the job.
SCHEMA_USER Schema used.
LAST_DATE Date when this job was last successfully run.
LAST_SEC Same as LAST_DATE.
THIS_DATE Date when this job started running.
THIS_SEC Same as THIS_DATE.
NEXT_DATE Date when the job will next run.
NEXT_SEC Same as NEXT_DATE.
TOTAL_TIME Seconds spent by the system running this job.
BROKEN If Y, this job will not be run.
INTERVAL A date function to calculate NEXT_DATE.
FAILURES Number of times job failed after the last successful run.
WHAT PL/SQL code that the job runs.

Steps

1. Run SQL*Plus and connect as the WAITE user account. CHP12_5.SQL, shown in Figure 12.5, creates a table and a stored procedure used in this How-To. The stored procedure is executed using the DBMS_JOB package and inserts a record into the table each time it runs.

The script creates a table containing a date column. The stored procedure created inserts a record with the date and time into the table each time it runs. Note the COMMIT statement at the end of the stored procedure, which enables you to determine when the stored procedure was last executed by querying the table.

2. Run CHP12_6.SQL file in SQL*Plus. The PL/SQL code contained in the file schedules the stored procedure to run every other minute throughout the day. The results of the operation are shown in Figure 12.6.

Line 2 declares a variable to be passed the job number as an OUT parameter from the SUBMIT function. Lines 4 through 9 call the DBMS_JOB.SUBMIT procedure to schedule the procedure. The first parameter, shown in line 5, is an OUT parameter that returns the job number. Unique job numbers are generated from the SYS.JOBSEQ sequence. The second parameter (string for WHAT), shown in line 6, specifies the procedure scheduled to run. The third parameter (value for NEXT_DATE), shown in line 7, is the date and time at which the job will next run.

The value of the parameter is the current date and time, plus one minute. If the value of the parameter is before SYSDATE, the job will never begin running. The fourth parameter (string for INTERVAL), shown in line 8, specifies a date function to calculate the time for the next execution of the job. This date function is evaluated before the job starts running and each time thereafter when the job completes successfully. The new date and time is then used as a value for NEXT_DATE. To execute a job only once, use ‘null’ for the INTERVAL parameter.

The last parameter, shown in line 9, specifies that the procedure called should be parsed when it is submitted (FALSE is the default). If TRUE, parsing is deferred until the procedure is run for the first time. Line 10 displays the unique job number returned by the SUBMIT procedure.

3. Run CHP12_7.SQL in SQL*Plus, as shown in Figure 12.7. The query contained in the file queries the USER_JOBS data dictionary view to show the scheduled jobs for the current user account.

The JOB column specified in line 2 returns the job number of the scheduled job. The NEXT_DATE column specified in line 3 is formatted with the TO_CHAR function to display both the date and time portions of the date. The value contained in the NEXT_DATE column is the next date the procedure will be executed. The function specified to calculate the next execution time of the job while submitting it is listed under the INTERVAL column. The BROKEN column in line 5 is a flag denoting whether the job is broken or not. Broken jobs are not scheduled to run again. The WHAT column shown in line 4 returns PL/SQL code that the job runs, of which only the name of the procedure to be executed is extracted for display.

4. The CHP12_8.SQL file queries the table created in Step 1. Each time the scheduled job is run, a record is inserted into the table. Run the statement to view records inserted into the table. Figure 12.8 displays the results of the query.

Line 2 returns the RUN_DATE column formatted with the TO_CHAR function to display the date and time the procedure was last executed. The FROM clause in lines 3 and 4 specifies that the table created in Step 1 is the source of data.

5. The REMOVE procedure is used to delete a job from the queue. To remove the job scheduled, simply execute the REMOVE procedure in the DBMS_JOB package. Pass the job number returned by the job created in Step 2 as a parameter to the REMOVE procedure.

SQL> EXEC DBMS_JOB.REMOVE(61);

PL/SQL successfully completed.

The REMOVE procedure receives the job number as its only parameter. The query to the USER_JOBS view used in Step 3 can be used to determine the job number. Once the DBMS_JOB.REMOVE procedure has been executed, the job will be no longer scheduled to run. In order to start the job again, the SUBMIT function must be executed.

How It Works

The DBMS_JOB package contains a complete set of procedures for managing job scheduling within Oracle. The SUBMIT procedure is used to submit a job for execution either once, or repeatedly. After jobs have been scheduled, the REMOVE procedure is used to remove a scheduled job from the queue. Jobs scheduled for execution by the user connected to the database can be found in the USER_JOBS data dictionary view.

Step 1 creates a sample table and stored procedure used in this How-To. The log table contains a DATE field used to track the execution times of the sample stored procedure. The stored procedure inserts the system date and time into the table each time it runs. Step 2 schedules the sample procedure to be executed every other minute, by specifying SYSDATE + 1/1440 as the execution interval time. Step 3 queries the USER_JOBS data dictionary view to display information about the job scheduled in the prior step. Step 4 queries the log table to view how often the scheduled job is running. The longer the job is allowed to run, the more the records are created in the sample table. Step 5 uses the REMOVE procedure from the DBMS_JOB package to cancel execution of the scheduled job.

Comments

The DBMS_JOB package lets you schedule batch processes independent of the operating system. Background processes in job queues are run by SNP processes. The number of SNP processes that Oracle runs in the background are specified as the JOB_QUEUE_PROCESSES=n parameter in the INIT<SID>.ORA file. Since the default is 0, make sure that you have this parameter set to the number of SNP processes you want Oracle to run; otherwise, no jobs will be executed.

The JOB_QUEUE_INTERVAL parameter in the INIT<SID>.ORA file is used to specify the interval in seconds for the SNP processes to check for new jobs in the job queue. Jobs can be exported and imported, and job numbers remain the same. The retry interval for running a failed job is doubled each time starting from the first retry interval of one minute. If attempts to run a job fails 16 times in a row, it is marked as broken and stops running. The USER_JOBS, ALL_JOBS, and DBA_JOBS provide information about jobs in the job queue. The DBA_JOBS_RUNNING view can be queried to list currently running jobs. Whenever possible, it is best to have Oracle schedule jobs because the scheduling code is portable to any platform Oracle runs on.