13.2.5.1.1 Exceptions.

The program does not raise any package exceptions. The interval date expression must evaluate to a future date or the following Oracle exception will be raised:

ORA-23420

Interval must evaluate to a time in the future.

13.2.5.1.2 Example.

This SQL*Plus script submits a job that uses DBMS_DDL.ANALYZE_OBJECT to analyze a particular table every day at midnight:

var jobno NUMBER

BEGIN
   DBMS_JOB.SUBMIT
      (job => :jobno
      ,what => 'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',''LOAD1'',''TENK'',
                   ''ESTIMATE'',null,estimate_percent=>50);'
      ,next_date => TRUNC(SYSDATE+1)
      ,interval => 'TRUNC(SYSDATE+1)'
      );
END;
/
print jobno

The what parameter must be enclosed in single quotes and the PL/SQL call terminated with a semicolon. To embed literal strings in the PL/SQL call, use two single quotes around the literal.

The no_parse parameter controls when the job's PL/SQL definition is actually parsed. The default value of FALSE specifies that the PL/SQL is parsed immediately when the job is submitted. Alternatively, if you specify TRUE, parsing can be deferred until the first execution of the job. This allows jobs to be submitted into the queue for future execution where objects necessary for execution (tables, packages, etc.) are not in place at the time of submission.

TIP: On some platforms and versions of Oracle, a COMMIT is required for the job to be picked up by the job queue for execution. If submitted jobs do not seem to be executing at all, this may be the cause. The workaround for this problem is to always COMMIT immediately after calling SUBMIT.

To execute a job one time only, pass a NULL value for the interval parameter.

13.2.5.2.1 Exceptions

The program does not raise any packaged exceptions. The interval date expression must evaluate to a future date or the following Oracle exception will be raised:

ORA-23420

Interval must evaluate to a time in the future.

The catalog table that records job queue entries is protected by a unique constraint on the job number. Therefore, using the ISUBMIT procedure to submit a job number that already exists results in the following error:

ORA-00001

Unique constraint (SYS.I_JOB_JOB) violated.

13.2.5.2.2 Example

The following example submits three jobs to the job queue, numbered 1, 2, and 3. Job 1 passes a string and number into procedure my_job1, runs it in one hour, and executes it every day thereafter. Job 2 passes a date into procedure my_job2, executes for the first time tomorrow, and execute it every 10 minutes thereafter. Job 3 is a PL/SQL block that does nothing, executes immediately, and will be removed from the queue automatically.

BEGIN
   DBMS_JOB.ISUBMIT
      (job  => 1
      ,what => 'my_job1(''string_parm_value'',120);'
      ,next_date => SYSDATE + 1/24
      ,interval => 'SYSDATE +1');

   DBMS_JOB.ISUBMIT
      (2, 'my_job2(date_IN=>SYSDATE);'
        ,SYSDATE+1,'SYSDATE+10/1440');

   DBMS_JOB.ISUBMIT(3,'BEGIN null; END;',SYSDATE,null);
END;

The ISUBMIT procedure allows the calling user or application to decide the job identification number. Collisions in job numbers will result in the unique constraint violation noted earlier. Therefore, it is probably better not to embed fixed job numbers into applications (as this will increase the chances for collisions) and to use SUBMIT instead of ISUBMIT. If specific job numbering is required, then you can minimize job number collisions by pushing the SYS.JOBSEQ sequence out to a number greater than those used in calls to ISUBMIT. This can be accomplished by consuming sequence numbers as follows:

SELECT SYS.JOBSEQ.NEXTVAL
  FROM dictionary
 WHERE rownum < 101;

After issuing the previous command, DBMS_JOB.SUBMIT will always return job numbers higher than 100. Note that in this command, the dictionary view is not special, but is used because it is publicly accessible and contains more than 100 rows. You can substitute any table or view accessible to the user.

WARNING: When you are using the ISUBMIT procedure, a subtle problem can arise from the fact that next_date has datatype VARCHAR2 instead of DATE (as in SUBMIT). When DATE values are assigned to next_date in calls to ISUBMIT, an implicit conversion to VARCHAR2 is done using the session's current NLS_DATE_FORMAT as the date mask. This can result in unexpected truncation of next_date. For instance, if the session NLS_DATE_FORMAT is `YY-MM-DD', dates assigned to next_date will be truncated to 12:00 a.m. To avoid this problem, either make sure that the session NLS_DATE_FORMAT includes all date components or supply next_date as a fully specified VARCHAR2.

13.2.6.1.1 Exceptions

The program does not raise any packaged exceptions. The interval date function must evaluate to a future date or the following Oracle exception will be raised:

ORA-23420

Interval must evaluate to a time in the future.

13.2.6.1.2 Restrictions

The CHANGE procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3, "Tips on Using DBMS_JOB"" section.

13.2.6.1.3 Example

The execution schedule of job 100 can be changed to next execute tomorrow at 6:00 a.m. and every two hours after that, as follows:

BEGIN
   DBMS_JOB.CHANGE(100,null,TRUNC(SYSDATE+1)+6/24,'SYSDATE+2/24');
END;
/

When the what parameter is changed to modify the actual job to execute, the user's current session NLS settings are also recorded and become part of the job's execution environment.

13.2.6.2.1 Exceptions

The program does not raise any package exceptions. The interval date expression must evaluate to a future date or the following Oracle exception will be raised:

ORA-23420

Interval must evaluate to a time in the future.

13.2.6.2.2 Restrictions

The INTERVAL procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3" section.

13.2.6.2.3 Example

The following SQL*Plus command will modify job 100 to execute every day at 6:00 a.m.:

SQL> execute DBMS_JOB.INTERVAL(100, 'TRUNC(SYSDATE+1)+6/24');

The date expression must be specified as a string literal or a VARCHAR2 variable containing a string literal. Literals that evaluate to PL/SQL functions are accepted by DBMS_JOB, but have been observed to cause erratic job execution behavior.

A job can be removed automatically from the job queue after its next execution by passing NULL for the interval parameter.

13.2.6.3.1 Restrictions

The NEXT_DATE procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3" section.

13.2.6.3.2 Example

This example shows a SQL*Plus example of how to schedule the next execution of job 100 for next Monday:

SQL> execute DBMS_JOB.NEXT_DATE(100, NEXT_DAY(SYSDATE,'MONDAY'));

When 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.

13.2.6.4.1 Restrictions

The WHAT procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3" section.

13.2.6.4.2 Example

In this example, job 100 is modified to execute a procedure called my_package.proc1. When the job is run by the job queue, it will run in a session that has NLS_DATE_FORMAT set as in the ALTER SESSION command.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY:MM:DD:HH24:MI:SS';

SQL> execute dbms_job.what(100,'my_package.proc1;');

When the what parameter is changed to modify the actual job to execute, the user's current session NLS settings are also recorded and become part of the job's execution environment.

The what parameter must be enclosed in single quotes and the PL/SQL call must be terminated with a semicolon. To embed literal strings in the PL/SQL call, use two single quotes around the literal.

13.2.7.1.1 Restrictions

The REMOVE procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3" section.

13.2.7.1.2 Example

To remove job number 100 from the job queue in SQL*Plus, specify the following:

SQL> execute DBMS_JOB.REMOVE(100);

When REMOVE is executed for a job that is currently executing, the job is removed from the job queue, but the current execution is allowed to complete. Terminating a running job and removing it from the job queue is described in the Section 13.3" section later in this chapter.

13.2.7.2.1 Restrictions

The BROKEN procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3" section.

13.2.7.2.2 Example

All jobs owned by the current user are set to broken by this PL/SQL block:

BEGIN
   FOR job_rec IN (SELECT job FROM user_jobs) 
   LOOP
      DBMS_JOB.BROKEN(job_rec.job,TRUE);
   END LOOP;
END;
/

Jobs are marked as broken by passing TRUE for the broken parameter. In this case, the next execution for the job date is automatically set to January 1, 4000, regardless of the value of the next_date parameter passed. Although it looks strange, this is not a problem and is merely another safeguard preventing the job queue processes from executing broken jobs.

When marking jobs as not broken by passing the value FALSE for the broken parameter, the value of next_date becomes the next execution date for the job. Since next_date has a default value of SYSDATE, marking a job as unbroken without specifying next_date explicitly indicates that the job should execute immediately. Be careful to pass an explicit value for next_date if immediate execution is not the desired behavior. Note also that DBMS_JOB.BROKEN (job,FALSE) will always modify the next execution date of the job, regardless of whether it was marked broken.

13.2.7.3.1 Restrictions

The RUN procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3" section.

13.2.7.3.2 Example

To run job number 100 immediately in the current session, specify the following:

SQL> execute DBMS_JOB.RUN(100);

The RUN procedure performs an implicit COMMIT in the current session. It runs the job with the current session's settings and privileges as the execution environment. Be aware that these could be different from the execution environment settings specified for the job and used by the job queue when it runs the job. This could cause unexpected results, so it is best to execute RUN from a session with the same environment as the job.

Also, issuing the RUN procedure computes the next execution date for the job using the current SYSDATE as the seed value. This could throw off the execution schedule of some jobs, depending on how the interval is defined. See Section 13.3" for a discussion of job intervals and date arithmetic.

13.2.8.1.1 Example.

This SQL*Plus script shows that current settings for the job definition parameters are placed into the mycall parameter of USER_EXPORT:

/* Filename on companion disk: job1.sql */*
set array 1
var job number
var jobstring VARCHAR2(2000)
col jobstring format a50 word_wrap
col what format a25 word_wrap
col interval format a20

ALTER SESSION SET NLS_DATE_FORMAT='YYYY:MM:DD:HH24:MI:SS';

BEGIN
   /* submit no-op job to execute every 30 seconds */
   DBMS_JOB.SUBMIT(:job,'begin null;end;',SYSDATE,'SYSDATE+1/2880');

   /* commit to make sure the submit "takes" */
   COMMIT;
   /* sleep for two minutes to let job execute a few times */
   DBMS_LOCK.SLEEP(120);
END;
/

SELECT job,what,next_date,interval
  FROM dba_jobs
 WHERE job = :job;

BEGIN
   /* export the job */
   DBMS_JOB.USER_EXPORT(:job,:jobstring);
END;
/

print jobstring

The following output was generated by the script. Notice that the current value of NEXT_DATE (as shown by querying DBA_JOBS) is extracted and placed into the string value returned in the mycall parameter as the value for next_date in the call to ISUBMIT.

Session altered.


PL/SQL procedure successfully completed.


   JOB WHAT                      NEXT_DATE           INTERVAL
------ ------------------------- ------------------- --------------
   175 begin null;end;           1997:11:16:16:22:59 SYSDATE+1/2880


PL/SQL procedure successfully completed.


JOBSTRING
-------------------------------------------------- 
dbms_job.isubmit(job=>175,what=>'begin
null;end;',next_date=>to_date('1997-11-16:16:22:59
','YYYY-MM-DD:HH24:MI:SS'),interval=>'SYSDATE+1/28
80',no_parse=>TRUE);