Viewing and Managing Job Queues in Oracle 9i - Page 3

Ajay Gursahani, ajay.gursahani@mahindrabt.com

DBMS_JOB.CHANGE

Alters a specified job that has already been submitted to the job queue. The job description, the time at which the job will be run, the interval between executions of the job, and the instance and force of a job can all be altered.

DBMS_JOB.CHANGE ( 
   job       IN  BINARY_INTEGER,
   what      IN  VARCHAR2,
   next_date IN  DATE,
   interval  IN  VARCHAR2,
   instance  IN  BINARY_INTEGER DEFAULT NULL,
   force     IN  BOOLEAN DEFAULT FALSE);

job - Number of the job being run.

what - PL/SQL procedure to run.

next_date - Date of the next refresh.

interval - Date function evaluated immediately before the job starts running.

instance - When a job is submitted, specifies which instance can run the job. This defaults to NULL, which indicates that instance affinity is not changed.

force - If this is FALSE, then the specified instance must be running. Otherwise, the routine raises an exception. If this is TRUE, then any positive integer is acceptable as the job instance.

Example:

EXECUTE DBMS_JOB.CHANGE(1,null,sysdate+3,null); 

Changes the next_date of job number 1 from 08/11/2002 to (system date+3).
If system date is 15/12/2002, then the next_date will be 18/12/2002.

SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;

JOB	SUBSTR(WHAT,1,35)	NEXT_DATE 	NEXT_SEC	INTERVAL 
-----------------------------------------------------------------------
1	HOUSEKEEP.DEL001;	18/12/2002 	12:35:22	sysdate+(59/(24*60*60))	
3	HOUSEKEEP.DEL003;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
4	DEPT.DELREC;		10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
5	HOUSEKEEP.DEL004;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	

4 rows selected.

DBMS_JOB.WHAT

The definition of a job can be altered by calling the DBMS_JOB.WHAT procedure.

DBMS_JOB.CHANGE ( 
   job    IN  BINARY_INTEGER,
   what   IN  VARCHAR2);

job - Number of the job being run.

what - PL/SQL procedure to run.

Example:

The following example changes the definition for job 3. The procedure DEL003 is replaced by procedure DEL006.

execute DBMS_JOB.WHAT(3,'HOUSEKEEP.DEL006;');

SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;


JOB	SUBSTR(WHAT,1,35)	NEXT_DATE 	NEXT_SEC	INTERVAL 
-----------------------------------------------------------------------
1	HOUSEKEEP.DEL001;	18/12/2002 	12:35:22	sysdate+(59/(24*60*60))	
3	HOUSEKEEP.DEL006;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
4	DEPT.DELREC;		10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
5	HOUSEKEEP.DEL004;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	

4 rows selected.