DBMS_JOB v11

Suggest edits

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

Advanced Server's implementation of DBMS_JOB is a partial implementation when compared to Oracle's version. The following table lists the supported DBMS_JOB procedures:

Function/ProcedureReturn TypeDescription
BROKEN(job, broken [, next_date ])n/aSpecify that a given job is either broken or not broken.
CHANGE(job, what, next_date, interval, instance, force)n/aChange the job’s parameters.
INTERVAL(job, interval)n/aSet the execution frequency by means of a date function that is recalculated each time the job is run. This value becomes the next date/time for execution.
NEXT_DATE(job, next_date)n/aSet the next date/time the job is to be run.
REMOVE(job)n/aDelete the job definition from the database.
RUN(job)n/aForces execution of a job even if it is marked broken.
SUBMIT(job OUT, what [, next_date [, interval [, no_parse ]]])n/aCreates a job and stores its definition in the database.
WHAT(job, what)n/aChange the stored procedure run by a job.

Before using DBMS_JOB, a database superuser must create the pgAgent and DBMS_JOB extension. Use the psql client to connect to a database and invoke the command:

CREATE EXTENSION pgagent;
CREATE EXTENSION dbms_job;

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.

Note

The database user must be the same that created a job and schedule to start the pgAgent server and execute the job.

The following examples use the following stored procedure, job_proc, which simply inserts a timestamp into table, jobrun, containing a single VARCHAR2 column.

CREATE TABLE jobrun (
    runtime VARCHAR2(40)
);

CREATE OR REPLACE PROCEDURE job_proc
IS
BEGIN
    INSERT INTO jobrun VALUES ('job_proc run at ' || TO_CHAR(SYSDATE,
        'yyyy-mm-dd hh24:mi:ss'));
END;

broken change interval next_date remove run submit what

← Prev

DBMS_CRYPTO

Next →

BROKEN