Oracle7 Server Distributed Systems Volume II: Replicated Data

Contents Index Home Previous Next

Submitting a Job to the Job Queue

To submit a new job to the job queue, use the SUBMIT procedure in the DBMS_JOB package. The SUBMIT procedure returns the number of the job you submitted.

Submitting a Job: Example

Submit a new job to the job queue. The job calls the procedure DBMS_DDL.ANALYZE_OBJECT to generate optimizer statistics for the table VALERIE.ACCOUNTS. The statistics are based on a sample of half the rows of the ACCOUNTS table. The job is run every 24 hours.

SVRMGR> VARIABLE jobno number;
SVRMGR> begin
     2>           DBMS_JOB.SUBMIT(:jobno, 
     3>                   'dbms_ddl.analyze_object(''TABLE'',
     4>                   ''VALERIE'', ''ACCOUNTS'', 
     5>                   ''ESTIMATE'', NULL, 50);', 
     6>                   SYSDATE, 'SYSDATE + 1');
     7> commit;
     8> end;
     9> /
Statement processed.
SVRMGR> print jobno
JOBNO
----------
     14144

Owner of a Job

When a user submits a job to the job queue, Oracle specifies that user as the owner of the job. Only a job's owner can alter the job, force the job to run, or remove the job from the queue. Ideally, the owner of the job will be the replication administrator, however, if it is necessary that a user submit the job, the user must have the appropriate privileges at the remote site.

In a deferred transaction, note that, unless explicitly specified otherwise, the user who initiated a transaction will determine which database link is used. The owner can be specified for a deferred transaction by using the DEFER_SYS.EXECUTE parameter, execute_as_user. See [*].

Job Numbers

A queued job is identified by its job number. When you submit a job, its job number is automatically generated from the sequence SYS.JOBSEQ.

Once a job is assigned a job number, that number does not change. Even if the job is exported and imported, its job number remains the same.

Job Definitions

The job definition is the PL/SQL code specified in the WHAT parameter of the SUBMIT procedure.

Attention: In the job definition, use two single quotation marks around strings. Always include a semicolon at the end of the job definition.

Normally, the job definition is a single call to a procedure. The procedure call can have any number of parameters.

There are special parameter values that Oracle recognizes in a job definition. Table 10 - 3 lists these parameters.

Parameter Mode Description
job IN The number of the current job.
next_date IN/OUT The date of the next execution of the job.
broken IN/OUT Status of job, broken or not broken. The IN value is FALSE.
Table 10 - 3. Special Parameter Values for Job Definitions

Job Definitions: Examples

The following examples show valid job definitions:

'myproc(''10-JAN-82'', next_date, broken);'
'scott.emppackage.give_raise(''KANE'', 3000.00);'
'dbms_job.remove(job);'

Job Execution Interval

Immediately before a job is executed, the INTERVAL date function is evaluated. If the job completes successfully, the date calculated from INTERVAL becomes the new NEXT_DATE. If the INTERVAL date function evaluates to NULL and the job completes successfully, the job is deleted from the queue.

If a job should be executed periodically at a set interval, use a date expression similar to 'SYSDATE + 7' in the INTERVAL parameter. For example, if you set the execution interval to 'SYSDATE + 7' on Monday, but for some reason (such as a network failure) the job is not executed until Thursday, 'SYSDATE + 7' now evaluates to every Thursday, not Monday.

If you always want to automatically execute a job at a specific time, regardless of the last execution (for example, every Monday), the INTERVAL and NEXT_DATE parameters should specify a date expression similar to 'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'')'.

Table 10 - 4 lists sample date expressions used for job execution intervals.

Date Expression Evaluation
'SYSDATE + 7' 7 days after the last execution
'SYSDATE + 1/48' 30 minutes after the last execution
'SYSDATE + 1/8640' 10 seconds after the last execution
'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24' Every Monday at 3PM
'NEXT_DAY(ADD_MONTHS (TRUNC(SYSDATE, ''Q''), 3), ''THURSDAY'')' First Thursday of each quarter
Table 10 - 4. Common Job Execution Intervals

Attention: When specifying NEXT_DATE or INTERVAL, remember that date literals and date strings must be enclosed in single quotation marks. Also, the value of INTERVAL must be enclosed in single quotes.

Additional Information: The parameters for the DBMS_JOBS.SUBMIT procedure are described in Table 12 - 36.

Contents Index Home Previous Next