DBMS_JOB

The DBMS_JOB package schedules and manages jobs in the job queue

DBMS_JOB.SUBMIT

To submit a job to the job queue, use the following syntax:

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

   instance  IN     BINARY_INTEGER DEFAULT ANY_INSTANCE,

   force     IN     BOOLEAN DEFAULT FALSE);

DBMS_JOB.INSTANCE

To assign a particular instance to execute a job, use the following syntax:

   DBMS_JOB.INSTANCE(  JOB IN BINARY_INTEGER,

     instance                IN BINARY_INTEGER,

     force                   IN BOOLEAN DEFAULT FALSE);

DBMS_JOB.CHANGE

To alter user-definable parameters associated with a job, use the following syntax:

   DBMS_JOB.CHANGE(  JOB IN BINARY_INTEGER,

   what                  IN VARCHAR2 DEFAULT NULL,

   next_date             IN DATE DEFAULT NULL,

   interval              IN VARCHAR2 DEFAULT NULL,

   instance              IN BINARY_INTEGER DEFAULT NULL,

   force                 IN BOOLEAN DEFAULT FALSE );

DBMS_JOB.RUN

The force parameter for DBMS_JOB.RUN defaults to FALSE. If force is TRUE, instance affinity is irrelevant for running jobs in the foreground process. If force is FALSE, the job can run in the foreground only in the specified instance. Oracle displays error ORA-23428 if force is FALSE and the connected instance is the incorrect instance.

   DBMS_JOB.RUN(

      job    IN BINARY_INTEGER,

      force  IN BOOLEAN DEFAULT FALSE);

CHANGE Procedure

This procedure changes any of the fields a user can set in a job.

Syntax

DBMS_JOB.CHANGE ( 

   job       IN  BINARY_INTEGER,

   what      IN  VARCHAR2,

   next_date IN  DATE,

   interval  IN  VARCHAR2,

   instance  IN  BINARY_INTEGER DEFAULT NULL,

   force     IN  BOOLEAN DEFAULT FALSE);

Example

BEGIN

   DBMS_JOB.CHANGE(14144, null, null, 'sysdate+3');

   COMMIT;

END;

INTERVAL Procedure

This procedure changes how often a job runs.

Syntax

DBMS_JOB.INTERVAL ( 

   job       IN  BINARY_INTEGER,

   interval  IN  VARCHAR2);

NEXT_DATE Procedure

This procedure changes when an existing job next runs.

Syntax

DBMS_JOB.NEXT_DATE ( 

   job       IN  BINARY_INTEGER,

   next_date IN  DATE);

REMOVE Procedure

This procedure removes an existing job from the job queue. This currently does not stop a running job.

Syntax

DBMS_JOB.REMOVE ( 

   job       IN  BINARY_INTEGER );

RUN Procedure

This procedure runs job JOB now. It runs it even if it is broken.
Running the job recomputes next_date. See data dictionary view USER_JOBS or DBA_JOBS.

Syntax

DBMS_JOB.RUN ( 

   job       IN  BINARY_INTEGER,

   force     IN  BOOLEAN DEFAULT FALSE);

SUBMIT Procedure

This procedure submits a new job. It chooses the job from the sequence sys.jobseq.

Syntax

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

   instance  IN  BINARY_INTEGER DEFAULT any_instance,

   force     IN  BOOLEAN DEFAULT FALSE);