[Appendix C] C.5 DBMS_ JOB - e-Reading Library

C.5 DBMS_ JOB

The DBMS_ JOB package provides a way for you to schedule jobs from within the Oracle RDBMS. A job is a call to a single stored procedure. You can submit a job to run once or on a recurring basis. Once a job has been submitted, you can check on the status of the job by viewing a data dictionary table. You can also change the parameters of the job with the CHANGE procedure. When you submit a job, you must provide a string that describes that job to the DBMS_ JOB package and specify a job execution interval.

C.5.1 The BROKEN procedure

The Oracle Server considers a job to be broken if it has tried and failed 16 times to run the job. At this point, Oracle marks the job as broken and will no longer try to run the job until either (a) you mark the job as fixed or (b) you force the job to execute with a call to the RUN procedure. Use the BROKEN procedure to mark the job as fixed and specify the next date on which you want the job to run. The specification is:

PROCEDURE DBMS_JOB.BROKEN    (job IN BINARY_INTEGER,     broken IN BOOLEAN,     next_date IN DATE DEFAULT SYSDATE);

C.5.2 The CHANGE procedure

Use the CHANGE procedure to change one or all of the attributes of a job. The specification is:

PROCEDURE DBMS_JOB.CHANGE    (job IN BINARY_INTEGER,     what IN VARCHAR2,     next_date IN DATE,     interval IN VARCHAR2);

C.5.3 The INTERVAL procedure

Use the INTERVAL procedure to change the interval for which a queued job is going to run. The specification is:

PROCEDURE DBMS_JOB.INTERVAL    (job IN BINARY_INTEGER,     interval IN VARCHAR2);

C.5.4 The ISUBMIT procedure

The ISUBMIT procedure submits a new job with a specified job number to the queue. The difference between ISUBMIT and SUBMIT (described later in this section) is that ISUBMIT specifies a job number, whereas SUBMIT returns a job number generated by the DBMS_JOB package. The specification is:

PROCEDURE DBMS_JOB.ISUBMIT    (job IN BINARY_INTEGER,    what IN VARCHAR2,    next_date in DATE DEFAULT SYSDATE    interval IN VARCHAR2 DEFAULT 'NULL',    no_parse in BOOLEAN DEFAULT FALSE);

C.5.5 The NEXT_DATE procedure

Use the NEXT_DATE procedure to change when a queued job is going to run. The specification is:

PROCEDURE DBMS_JOB.NEXT_DATE    (job IN BINARY_INTEGER,     next_date IN DATE);

C.5.6 The REMOVE procedure

Use the REMOVE procedure to remove a job from the queue. If the job has started execution, you cannot remove it from the queue. The specification is:

PROCEDURE DBMS_JOB.REMOVE (job IN BINARY_INTEGER);

C.5.7 The RUN procedure

Use the RUN procedure to force a job to be executed immediately, regardless of the values for next_date and interval stored in the job queue. The specification is:

PROCEDURE DBMS_JOB.RUN (job IN BINARY_INTEGER);

C.5.8 The SUBMIT procedure

The SUBMIT procedure submits jobs to the queue. The specification is:

PROCEDURE 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);

C.5.9 The USER_EXPORT procedure

The USER_EXPORT procedure is used to extract the job string from a job in the queue. The specification is:

PROCEDURE DBMS_JOB.USER_EXPORT    (job IN BINARY_INTEGER,     mycall OUT VARCHAR2);

C.5.10 The WHAT procedure

Use the WHAT procedure to change what a queued job is going to run. The specification is:

PROCEDURE DBMS_JOB.WHAT    (job IN BINARY_INTEGER,     what IN VARCHAR2);


C.4 DBMS_DDL		C.6 DBMS_LOB (PL/SQL8 Only)