DBMS_JOB v15

Suggest edits

The DBMS_JOB package lets you create, schedule, and manage jobs. A job runs a stored procedure that was previously stored in the database. The SUBMIT procedure creates and stores a job definition. A job identifier is assigned to a job with a stored procedure and the attributes describing when and how often to run the job.

This package relies on the pgAgent scheduler. By default, the EDB Postgres Advanced Server installer installs pgAgent, but you must start the pgAgent service manually before using DBMS_JOB. If you attempt to use this package to schedule a job after uninstalling pgAgent, DBMS_JOB reports an error. DBMS_JOB verifies that pgAgent is installed but doesn't verify that the service is running.

EDB Postgres 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 to run the job.
REMOVE(job)n/aDelete the job definition from the database.
RUN(job)n/aForce execution of a job even if it's marked broken.
SUBMIT(job OUT, what [, next_date [, interval [, no_parse ]]])n/aCreate a job and store 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 runs depends on two interacting parameters: next_date and interval. The next_date parameter is a date/time value that specifies the next date/time to execute the job. The interval parameter is a string that contains a date function that evaluates to a date/time value.

Before the job executes, 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 reevaluated before each job executes, supplying the next_date date/time for the next execution.

Note

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

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

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