13.1.2.1 The job parameter

The job parameter is an integer that uniquely identifies the job. It can be either selected by the user or automatically assigned by the system, depending on which of the two job submission procedures is used to enter the job into the job queue. The DBMS_JOB.SUBMIT procedure automatically assigns the job number by obtaining the next value from the sequence SYS.JOBSEQ. It is returned as an OUT parameter so the caller can subsequently identify the job submitted. DBMS_JOB.ISUBMIT allows the user to assign a specific integer identifier to the job, and it is up to the caller to ensure that this number is unique.

Job numbers cannot be changed other than by removing and resubmitting the job. The job number is retained even when the database is exported and imported. Be aware of the potential for job number conflicts when performing export/import between databases that contain jobs.

13.1.2.2 The what parameter

The what parameter is a character string that evaluates to a valid PL/SQL call to be executed automatically by the job queue. You must enclose the what parameter in single quotes if you are using a string literal. Alternatively, you can use a VARCHAR2 variable containing the desired string value. The actual PL/SQL call must be terminated with a semicolon. To embed literal strings in the PL/SQL call, include two single quotes around the literal.

The length of the what parameter is limited to 2000 bytes under Oracle 7.3 and 4000 bytes under Oracle 8.0. These limits should be more than sufficient for all practical purposes. The value of the parameter is normally a call to a stored PL/SQL program. It is best to avoid using large anonymous PL/SQL blocks, although these are legal values. Another good tip is to always wrap stored procedure invocations in an anonymous block, as some subtle difficulties are possible otherwise. Thus, instead of:

what => 'my_proc(parm1);'

it is safer to use:

what => 'begin my_proc(parm1); end;'

Whenever the what parameter is modified to change the job to execute, the user's current session settings are recorded and become part of the job's execution environment. This could alter the expected execution behavior of the job if the session settings were different from those in place when the job was originally submitted. It is important to be aware of this potential side effect and be sure that session settings are correct whenever the what parameter is used in a DBMS_JOB procedure call. See the Section 13.2" section for more discussion of the job execution environment.

Jobs that reference database links will fail if the database link is not fully qualified with the username and password. This is another subtle consequence of the execution environment of jobs.

The job definition specified by the what parameter can also reference the following "special" job parameter values:

When the job definition references these job parameters in its own parameter list, their values are assigned to the parameters in the job definition when the job executes. For example, suppose that a procedure called proc1 has the following specification:

PROCEDURE proc1 (my_job_number IN INTEGER);

Suppose also that we submit proc1 to be executed by the job queue as follows:

DECLARE
   jobno   INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jobno,`proc1(my_job_number=>job);');
END;
/

When proc1 is executed by the queue, the my_job_number parameter is assigned the job's job number, and thus proc1 will "know" what job number it is.

The ability to reference and modify job parameters from within the job itself enables the creation of self-modifying and self-aware jobs. See the Section 13.4, "DBMS_JOB Examples"" section for an example of a job that demonstrates these powerful characteristics.

13.1.2.3 The next_date parameter

The next_date parameter tells the job queue when a job should be executed next. This parameter defaults to SYSDATE in both the DBMS_JOB.SUBMIT and BROKEN procedures, indicating that the job should be run immediately.

Whenever a NULL value is passed for the next_date parameter, the next execution date for the job is set to January 1, 4000. This effectively keeps the job from being executed without removing it from the job queue.

The next_date parameter can be set to a time in the past. Jobs are chosen for execution in order of their next execution dates, so setting a job's next_date back can effectively move the job ahead in the queue. This can be useful in systems where the job queue processes are not keeping up with jobs to be executed, and a specific job needs to be executed as soon as possible.

13.1.2.4 The interval parameter

The interval parameter is a character string representing a valid Oracle date expression. This date expression is evaluated each time the job begins execution. When a job completes successfully, this date becomes the next execution date for the job. It is important to remember that interval evaluation and updating the job's next execution date happen at different times. For instance, a job that takes one hour to complete and has interval set to SYSDATE+1/48 (every 30 minutes) will constantly execute, because each time it completes, it will already be 30 minutes late to execute again.

The interval expression must evaluate to either a NULL value or a time in the future. When interval evaluates to a NULL value, the job will not be re-executed after the next execution and will be automatically removed from the job queue. Thus, to execute a job one time only, pass a NULL value for the interval parameter.

Jobs may have complex execution schedules, requiring complex date arithmetic expressions for the interval parameter. The interval parameter can contain a call to a PL/SQL function with a return datatype of DATE, suggesting a nice way to encapsulate complex execution schedules within simple interval parameter values. However, experimentation with using function calls for interval resulted in erratic job execution behavior. Thus, unfortunately, a useful alternative to embedding complex date arithmetic into the interval parameter does not appear to be currently available.

13.1.2.5 The broken parameter

The broken parameter is a BOOLEAN flag used to indicate that the job is to be marked as broken (TRUE) or unbroken (FALSE). The job queue processes will not attempt to execute jobs marked as broken.