DBMS_JOB v16

Suggest edits

The DBMS_JOB package's jobs are scheduled and run in the background by the edb_job_scheduler extension. For more information, see EDB Job Scheduler extension.

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.

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.

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. The expression in interval is repeatedly reevaluated before each job executes, supplying the next_date date/time for the next execution.

These 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

RANDOMNUMBER

↑ Up

Built-in packages

Next →

BROKEN