DBMS_SCHEDULER as a new alternative for DBMS_JOB

Many people have a dislike of Dbms_job. Setting the right parameters
requires good knowledge of the input parameters and you probably need a
calculator too! In Oracle 10g, a new package might make things easier. 

What’s in a name

If you check 10g’s data dictionary, you’ll find a new package by the name dbms_scheduler.
One of the most obvious improvements is the naming convention of the
functions in this package. DBMS_JOB.Submit is now named
DBMS_SCHEDULER.Create_job and DBMS_JOB.Run goes by the name
DBMS_SCHEDULER.Run_job. You may disagree, but I think the new names
make a lot more sense.

In control of intervals

Fortunately,
Oracle made major improvements in the most annoying flaw of DBMS_JOB:
defining the interval. For instance, if you want a job to start every
morning at 06:00 exactly, the parameter “interval” usually contains
something like ‘TRUNC(SYSDATE) + 1 + 21/24’. With DBMS_SCHEDULER, the new parameter “repeat_interval” is set to ‘FREQ=DAILY’. In combination with a start_date = ’20-APR-2006 06:00′ the job starts at 06:00. Everyday at
06:00. And not at 06:10:14 because the previous run lasted 10 minutes
14 seconds and you entered an interval of “SYSDATE + 1”.

Scheduler and program objects

Another notable thing is the option to create scheduler and program objects. This is quite interesting too, because now you can define a custom schedule which you can reuse in all jobs.

This example creates a schedule which runs every hour on the half hour.

BEGIN
  DBMS_SCHEDULER.create_schedule
  ( schedule_name   => 'run_every_hour'
  , start_date      => SYSTIMESTAMP
  , repeat_interval => 'freq=hourly; byminute=30'
  , end_date        => NULL
  , comments        => 'This schedule starts every hour at the half hour'
  ) ;
END;
/

Next step is creating a program object with parameters:

BEGIN
  dbms_scheduler.create_program
    ( program_name        => 'Exampleprogram'
    , program_type        => 'STORED_PROCEDURE'
    , program_action      => 'package.procedure'
    , number_of_arguments => 1
    , enabled             => FALSE
    , comments            => 'A demo program object.' );
      
  dbms_scheduler.define_program_argument
    ( program_name      => 'Exampleprogram'
    , argument_position => 1
    , argument_name     => 'parameter_1'
    , argument_type => 'VARCHAR2'
    , default_value => 'Scott'
    ) ;
    dbms_scheduler.enable( name => 'Exampleprogram' );
END;
/

Creating a job is now easier than ever:

BEGIN 
  DBMS_SCHEDULER.create_job
  ( job_name      => 'Examplejob'
  , program_name  => 'Exampleprogram'
  , schedule_name => 'run_every_hour'
  , enabled       => FALSE
  , comments      => 'Job that starts according to predefined schedule'
  ) ;

  dbms_scheduler.set_job_argument_value
  ( job_name => 'Examplejob'
  , argument_position => 1
  , argument_value => 'Tiger' );

  dbms_scheduler.enable( 'Examplejob' );

  commit;

END;
/
The parameter program_type can be one of the values:
‘PLSQL_BLOCK’, ‘STORED_PROCEDURE’ or ‘EXECUTABLE’.  With ‘executable’, dbms_scheduler allows to execute shell scripts and
executables.

Ooops…

Please
note that in Oracle 10g R1 there is a vulnerability in which user with
access to DBMS_SCHEDULER and the create job privilege are able to gain
DBA and remote OS rights. Pete Finnigan found the bug and there is a
patch available as described here.