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<br />  DBMS_SCHEDULER.create_schedule<br />  ( schedule_name   =&gt; 'run_every_hour'<br />  , start_date      =&gt; SYSTIMESTAMP<br />  , repeat_interval =&gt; 'freq=hourly; byminute=30'<br />  , end_date        =&gt; NULL<br />  , comments        =&gt; 'This schedule starts every hour at the half hour'<br />  ) ;<br />END;<br />/

Next step is creating a program object with parameters:

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

Creating a job is now easier than ever:

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

  dbms_scheduler.set_job_argument_value<br />  ( job_name =&gt; 'Examplejob'<br />  , argument_position =&gt; 1<br />  , argument_value =&gt; 'Tiger' );<br /><br />  dbms_scheduler.enable( 'Examplejob' );<br /><br />  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.