288-292

Previous

Table of Contents

Next

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.

Scheduling A Job

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.

Previous

Table of Contents

Next