Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
   
 

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 Broken() procedure

  • The Change() procedure

  • The Interval() procedure

  • The ISubmit() procedure

  • The Next_Date() procedure

  • The Remove() procedure

  • The Run() procedure

  • The Submit() procedure

  • The User_Export() procedure

  • The What() procedure

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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter