 |
|
Scheduling a Job
Oracle Tips by
Burleson
|
Now let’s look at an example of scheduling a typical job using the
DBMS_Job package. Assume that we have an Hourly_Tracking()
procedure that we want to run every hour. We schedule this procedure
to be run using a call to the Submit() procedure, as follows:
DBMS_Job.Submit (job => biJobNumber,
what => 'Hourly_Tracking;',
next_date => SYSDATE,
interval => 'SYSDATE + 1/24',
no_parse => FALSE);
This call schedules a job that will be executed immediately and
then every hour on the hour. The string passed to the interval
parameter equates to “the current date and time plus 1 day divided by
24.”
If an error occurs while the Hourly_Tracking() procedure is
executing, the procedure will halt, and the job will be marked as
broken. To restart the job, call the Broken() procedure, as
follows:
DBMS_Job.Broken (job => biJobNumber,
broken => FALSE,
next_date => SYSDATE);
By passing FALSE for the broken parameter, the job is
marked as unbroken and will be executed again at the specified
next_date.
At the end of the day, we want to stop the hourly execution of the
procedure before beginning our nightly backups. This is accomplished
by calling the Remove() procedure, as follows:
DBMS_Job.Remove (job => biJobNumber);
DBMS_Output
The DBMS_Output package is more familiar to PL/SQL
developers than any other package provided by Oracle. The routines
contained in this package are often used when debugging stored PL/SQL
objects. Consequently, this package is discussed in Chapter 8.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
