7.3 DBMS_JOB

Table of Contents Previous Next


7 Built-In Packages : 7.3 DBMS_JOB

The DBMS_JOB package provides for the creation, scheduling, and managing of jobs. A job runs a stored procedure which has been previously stored in the database. The SUBMIT procedure is used to create and store a job definition. A job identifier is assigned to a job along with its associated stored procedure and the attributes describing when and how often the job is to be run.
This package relies on the pgAgent scheduler. By default, the Postgres Plus Advanced Server installer installs pgAgent, but you must start the pgAgent service manually prior to using DBMS_JOB. If you attempt to use this package to schedule a job after un-installing pgAgent, DBMS_JOB will throw an error. DBMS_JOB verifies that pgAgent is installed, but does not verify that the service is running.
BROKEN(job, broken [, next_date ])
CHANGE(job, what, next_date, interval, instance, force)
INTERVAL(job, interval)
NEXT_DATE(job, next_date)
RUN(job)
SUBMIT(job OUT, what [, next_date [, interval [, no_parse ]]])
WHAT(job, what)
Postgres Plus Advanced Server's implementation of DBMS_JOB is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.
When and how often a job is run is dependent upon two interacting parameters – next_date and interval. The next_date parameter is a date/time value that specifies the next date/time when the job is to be executed. The interval parameter is a string that contains a date function that evaluates to a date/time value.
Just prior to any execution of the job, the expression in the interval parameter is evaluated. The resulting value replaces the next_date value stored with the job. The job is then executed. In this manner, the expression in interval is repeatedly re-evaluated prior to each job execution, supplying the next_date date/time for the next execution.
The following examples use the following stored procedure, job_proc, which simply inserts a timestamp into table, jobrun, containing a single VARCHAR2 column.
7.3.1 BROKEN
The BROKEN procedure sets the state of a job to either broken or not broken. A broken job cannot be executed except by using the RUN procedure.
BROKEN(job BINARY_INTEGER, broken BOOLEAN [, next_date DATE ])
If set to TRUE the job’s state is set to broken. If set to FALSE the job’s state is set to not broken. Broken jobs cannot be run except by using the RUN procedure.
7.3.2 CHANGE
The CHANGE procedure modifies certain job attributes including the stored procedure to be run, the next date/time the job is to be run, and how often it is to be run.
CHANGE(job BINARY_INTEGER what VARCHAR2, next_date DATE,
interval VARCHAR2, instance BINARY_INTEGER, force BOOLEAN)
7.3.3 INTERVAL
The INTERVAL procedure sets the frequency of how often a job is to be run.
INTERVAL(job BINARY_INTEGER, interval VARCHAR2)
7.3.4 NEXT_DATE
The NEXT_DATE procedure sets the date/time of when the job is to be run next.
NEXT_DATE(job BINARY_INTEGER, next_date DATE)
7.3.5 REMOVE
The REMOVE procedure deletes the specified job from the database. The job must be resubmitted using the SUBMIT procedure in order to have it executed again. Note that the stored procedure that was associated with the job is not deleted.
REMOVE(job BINARY_INTEGER)
7.3.6 RUN
The RUN procedure forces the job to be run, even if its state is broken.
RUN(job BINARY_INTEGER)
7.3.7 SUBMIT
The SUBMIT procedure creates a job definition and stores it in the database. A job consists of a job identifier, the stored procedure to be executed, when the job is to be first run, and a date function that calculates the next date/time the job is to be run.
SUBMIT(job OUT BINARY_INTEGER, what VARCHAR2
[, next_date DATE [, interval VARCHAR2 [, no_parse BOOLEAN ]]])
Date function that when evaluated, provides the next date/time the job is to run. If interval is set to null, then the job is run only once. Null is the default.
If set to TRUE, do not syntax-check the stored procedure upon job creation – check only when the job first executes. If set to FALSE, check the procedure upon job creation. The default is FALSE.
Note: The no_parse option is not supported in this implementation of SUBMIT(). It is included for compatibility only.
The following example creates a job using stored procedure, job_proc. The job will execute immediately and run once a day thereafter as set by the interval parameter, SYSDATE + 1.
The job immediately executes procedure, job_proc, populating table, jobrun, with a row:
7.3.8 WHAT
The WHAT procedure changes the stored procedure that the job will execute.
WHAT(job BINARY_INTEGER, what VARCHAR2)
Change the job to run the list_emp procedure:

7 Built-In Packages : 7.3 DBMS_JOB

Table of Contents Previous Next