| Previous | Table of Contents | Next |
Dialing in on the weekends to run jobs is no fun and completely unnecessary. Even if you’re on an operating system that does not adequately support job scheduling, you can let the database take care of itself by using the DBMS_JOB package. Spend the weekend skiing instead.
DBMS_JOB
This package allows you to schedule PL/SQL processes to be run from a job queue. A job is put into the job queue with parameters to specify when it is to run and how often. Information on jobs is kept in the data dictionary views dba_jobs, user_jobs, and dba_jobs_running.
Background processes called SNP processes are used to execute jobs in the job queue. One process is required for each simultaneously executed job. In Oracle 7 only 10 processes could be created, but in Oracle8 there can be 36. These processes are named SNP0 through SNP9 and SNPA through SNPZ. There are three init.ora parameters that control the behavior of these processes. They are as follows:
| JOB_QUEUE_PROCESSES | Creates 0–36 SNP processes at instance startup. |
| JOB_QUEUE_INTERVAL | Seconds to sleep before checking for a new job, from 1 to 3,600. |
| JOB_QUEUE_KEEP_CONNECTIONS | TRUE means connections made to remote databases are kept open until the SNP process is stopped. FALSE means remote connections are closed as soon as all the jobs are run. |
PROCEDURE SUBMIT(
job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE default SYSDATE,
interval IN VARCHAR2 default NULL,
no_parse IN BOOLEAN default FALSE);
| job | Unique identifier assigned to the job. This number does not change as long as the job exists. |
| what | PL/SQL code—usually a stored procedure call. |
| next_date | Date to run the job next. |
| interval | Calculation used to determine next runtime. For example, 'SYSDATE + 1/12’ would run the job every two hours starting from next_date. |
| no_parse | TRUE defers parsing of PL/SQL until it executes the first time. FALSE parses the code when it is submitted. |
This process submits a job to the job queue. Once a job is submitted, it can be run directly by using the DBMS_JOB.RUN procedure. The current environment settings, like NLS parameters, are recorded when the job is submitted. These settings are called the “job execution environment,” and they persist for that job until a session with a different set of environment settings makes a change to it.
PROCEDURE RUN(
job IN BINARY_INTEGER);
Used to directly run jobs as specified by the job number they are assigned when they are submitted. Jobs cannot be run before they are submitted to the job queue.
When a job is submitted the current environment is recorded with it, and it is always run with those same environment settings.
PROCEDURE BROKEN(
job IN BINARY_INTEGER,
broken IN BOOLEAN,
next_date IN DATE default SYSDATE);
| job | Job number of job to change. |
| broken | TRUE sets the job’s status to broken and it will no longer be run. FALSE resets the broken status for the job so it will be run again. |
| next_date | Date to run the job again. |
This procedure is used to manually flag a job as broken or not broken. Jobs that fail are automatically run again at intervals, starting with one minute and doubling until the job has failed 16 times. After 16 failures, the job is marked as broken and no further attempts are made to run it. Broken jobs can be run directly using DBMS_JOB.RUN.
PROCEDURE REMOVE(
job IN BINARY_INTEGER);
This procedure removes a job from the job queue.
Note:
CHANGE, WHAT, NEXT_DATE, and INTERVAL all cause the prevailing environment settings to be associated with the job, just as they are when the job is first submitted.
PROCEDURE CHANGE(
job IN BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE,
interval IN VARCHAR2);
This procedure is used to change multiple aspects of a job. The following procedures allow you to change the individual job attributes they refer to:
PROCEDURE WHAT(
job IN BINARY_INTEGER,
what IN VARCHAR2);
PROCEDURE NEXT_DATE(
job IN BINARY_INTEGER,
next_date IN DATE);
PROCEDURE INTERVAL(
job IN BINARY_INTEGER,
interval IN VARCHAR2);
I hope the information in this chapter is enough to get you started in the use of these packages and has given you some idea of their overall capabilities.
Although they can seem cumbersome to work with, once you get into using the packages supplied with Oracle8, you will begin to see some of the possibilities for building useful applications in PL/SQL. There really is no need to reach beyond PL/SQL for scheduling, communications, logging, DDL, or Dynamic SQL.
| Previous | Table of Contents | Next |