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.
