The Scheduler

Values for repeat_interval

select job, log_user, next_date, next_sec, interval, what
from dba_jobs
where job=152152;

       JOB LOG_USER   NEXT_DATE  NEXT_SEC INTERVAL                  WHAT
---------- ---------- ---------- -------- ------------------------- --------------------
    152152 SCOTT      05/01/2006 01:00:00 trunc(sysdate+1) + 1/24   SCOTTS_PROC;

DBMS_SCHEDULER.CREATE_JOB is an overloaded procedure; in this example, we'll look at just one of many ways to call it. Here's the call:

begin
dbms_scheduler.create_job
 (job_name => 'SCOTT.RUN_SCOTTS_PROC',
  job_type => 'STORED_PROCEDURE',
  job_action=> 'SCOTT.SCOTTS_PROC',
  start_date=> trunc(sysdate+1)+1/24,
  repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT; BYHOUR=1;',
  enabled=>true,
  auto_drop=>false,
  comments=>'Converted from job 152152');
end;

There are a lot of things to notice here; let's go through the call line by line.

1. Instead of a number, the job's identified by the name you pass as job_name. Job names follow the standard Oracle naming conventions. SYS can create a job for anyone. So, job_name => 'SCOTT.RUN_SCOTTS_PROC' creates a job in SCOTT's schema named RUN_SCOTTS_PROC .
2. You need to specify the job type. As in the old job scheduling mechanism, you can schedule anonymous blocks (job_type: 'PLSQL_BLOCK') or stored procedures (job_type: 'STORED_PROCEDURE'). You can also schedule programs that run outside the database (job_type: 'EXECUTABLE') or schedule "job chains" (job_type: 'CHAIN'). A job chain is a sequence of jobs.
3. job_action is the equivalent of "what" in dbms_job.submit. Note that if you're scheduling a stored procedure, you don't put a semicolon after the procedure name, as you did in dbms_job.submit . Note also that if you want to pass arguments to a stored procedure, like UPDATE_INDEXES('SCOTT'), you have to either wrap the stored procedure in an anonymous pl/sql block, or set the arguments with a separate call to SET_JOB_ARGUMENT_VALUE - a topic we won't cover here.
4. start_date is the first date the job will execute. Note that if you specify the repeat_interval using calendaring syntax, which we'll cover shortly, start-date is used as a reference only - the job won't actually execute until the next date that matches your repeat_interval. For example, if you set the repeat interval to every Thursday, and pass a start date that's on a Tuesday, then the job will execute on the first Thursday following the start date.
5. This call passes repeat_interval in calendaring syntax, which we'll discuss below. You could also pass it just as you had it in DBA_JOBS, as 'trunc(sysdate+1) + 1/24'.
6. In DBMS_SCHEDULER, unlike DBMS_JOB, the default is that new jobs are disabled. If you want your job to be enabled as soon as you create it, as it would have been with DBMS_JOB, then passenabled=>true.
7. You can control whether or not a one-time job is dropped after it's run by setting auto_drop to FALSE.
8. You can pass in comments, which are displayed alongside the job in the static data dictionary view that shows Scheduler jobs.

After you run this call to create_job, there's one more thing to notice: you don't have to COMMIT. The results are immediately visible to all users in the DBA_SCHEDULER_JOBS table:

OWNER JOB_NAME        JOB_ACTION        START_DATE REPEAT_INTERVAL STATE     COMMENTS
----- --------------- ----------------- ---------- --------------- --------- -------------------------
SCOTT RUN_SCOTTS_PROC SCOTT.SCOTTS_PROC 05/08/2006 FREQ=DAILY; BYD SCHEDULED Converted from job 152152

What if you want to run this job right away? Use dbms_scheduler.run_job:

connect scott/tiger
begin
DBMS_SCHEDULER.RUN_JOB (
   job_name => 'RUN_SCOTTS_PROC',
   use_current_session => false);
end;
/

Note that the default for use_current_session is TRUE -- that is, if you don't pass this parameter, RUN_SCOTTS_PROC will run synchronously, ie. in your current session, instead of runningasynchronously, ie. being picked up and run by a job slave.