 |
|
DBMS_Job Package
Oracle Tips by
Burleson
|
The DBMS_Job package allows developers to schedule execution
of PL/SQL code at a later time. Using the DBMS_Job package
requires your DBA to set up some parameters in the init.ora file;
consult with your DBA to determine if the database is set up for the
use of this package.
The package contains the following 10 procedures:
The simplest sequence of events for running a job
is very straightforward. The developer calls the Submit()
procedure. The developer doesn’t have to do any further tasks. At the
scheduled time, Oracle will execute the specified job.
Unfortunately, things don’t always work as expected. For instance,
a job becomes broken if an error occurs while the job is
executing. It’s possible that a job that has already been submitted
needs to be altered or canceled entirely. When these things happen,
the other procedures within the package come into play.
The Broken() Procedure
The Broken() procedure is used to update the status of a job
that has already been submitted, typically to mark a broken job as
unbroken. The procedure has three parameters: job, broken,
and next_date.
PROCEDURE Broken (job IN binary_integer,
broken IN boolean,
next_date IN date := SYSDATE)
The job parameter is the job number that uniquely identifies
the job in question. The broken parameter indicates whether or
not the job will be marked as broken—TRUE means that the job
will be marked as broken, and FALSE means that the job will be
marked as unbroken. The next_date parameter indicates the time
at which the job will be run again. This parameter defaults to the
current date and time.
The Change() Procedure
The Change() procedure is used to alter the settings for a
specific job. The procedure has four parameters: job, what,
next_date, and interval.
PROCEDURE Change (job IN binary_integer,
what IN varchar2,
next_date IN date,
interval IN varchar2)
Once again, the job parameter is the integer value that
uniquely identifies the job. The what parameter is a block of
PL/SQL code that is to be run by the job. The next_date
parameter indicates when the job will be executed. The interval
parameter indicates how often a job will be re-executed.
The Interval() Procedure
The Interval() procedure is used to explicitly set the
amount of time between re-executions of a job. The procedure has two
parameters: job and interval.
PROCEDURE Interval (job IN binary_integer,
interval IN varchar2)
The job parameter identifies a specific job. The interval
parameter indicates how often a job will be re-executed.
The ISubmit() Procedure
The ISubmit() procedure is used to submit a job with a
specific job number. The procedure has five parameters: job,
what, next_date, interval, and no_parse.
PROCEDURE ISubmit(job IN binary_integer,
what IN varchar2,
next_date IN date,
interval IN varchar2,
no_parse IN boolean := FALSE)
The only difference between this procedure and the Submit()
procedure is that the job parameter is passed as an IN
parameter and includes a job number specified by the developer. If the
specified job number is already used, an error will occur.
The Next_Date() Procedure
The Next_Date() procedure is used to explicitly set the
execution time for a job. The procedure accepts two parameters: job
and next_date.
PROCEDURE Next_Date (job IN binary_integer,
next_date IN date)
The job parameter identifies an existing job. The
next_date parameter specifies the date and time when the job
should be executed.
The Remove() Procedure
The Remove() procedure is used to remove a job that is
scheduled to run. The procedure accepts a single parameter:
PROCEDURE Remove (job IN binary_integer);
The job parameter uniquely identifies a job. The value of
this parameter is the value of the job parameter returned by
the call to the Submit() procedure for the job.
Jobs that are already running cannot be removed by calling this
procedure.
The Run() Procedure
The Run() procedure is used to immediately execute a
specified job. The procedure accepts only one parameter:
PROCEDURE Run (job IN binary_integer)
The job parameter identifies the job that is to be executed
immediately.
The Submit() Procedure
Jobs are normally scheduled using the Submit() procedure.
The procedure has five parameters: job, what,
next_date, interval, and no_parse.
PROCEDURE Submit(job OUT binary_integer,
what IN varchar2,
next_date IN date,
interval IN varchar2,
no_parse IN boolean := FALSE)
The job parameter is a binary_integer returned by the
Submit() procedure. This value is used to uniquely identify a
job. The what parameter is the block of PL/SQL code that will
be executed. The next_date parameter indicates when the job
will run. The interval parameter determines when the job will
be re-executed. The no_parse parameter indicates whether the
job should be parsed at submission time or execution time—TRUE
indicates that the PL/SQL code should be parsed when it is first
executed, and FALSE indicates that the PL/SQL code should be
parsed immediately.
The User_Export() Procedure
The User_Export() procedure returns the command used to
schedule an existing job so the job can be resubmitted. The procedure
has two parameters: job and my_call.
PROCEDURE User_Export (job IN binary_integer,
my_call IN OUT varchar2)
The job parameter identifies a scheduled job. The my_call
parameter holds the text required to resubmit the job in its current
state.
The What() Procedure
The What() procedure allows you to reset the command that is
run when the job executes. The procedure accepts two parameters:
job and what.
PROCEDURE What (job IN binary_integer,
what IN varchar2)
The job parameter identifies an existing job. The what
parameter holds the new PL/SQL code that will be executed.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.
|
