|Previous||Table of Contents||Next|
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 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 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 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 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.
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 timeTRUE 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 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 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.
Now lets 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);
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|