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

Ajay Gursahani, ajay.gursahani@mahindrabt.com

Managing Job Queues

This section describes the various aspects of managing job queues through the DBMS_JOB package viz:

• Submitting a Job to the Job Queue
• Removing a Job from the Job Queue
• Altering a Job
• Broken Jobs
• Forcing a Job to Execute

The DBMS_JOB package is used to schedule and manage jobs in the job queue. To achieve this we have to use procedures supplied under the DBMS_JOB package.

DBMS_JOB.SUBMIT

To submit a job to the job queue, use the following syntax:

DBMS_JOB.SUBMIT ( JOB OUT BINARY_INTEGER,
                  WHAT IN VARCHAR2, 
                  NEXT_DATE IN DATE DEFAULTSYSDATE, 
                  INTERVAL IN VARCHAR2 DEFAULT 'NULL',
                  NO_PARSE IN BOOLEAN DEFAULT FALSE,
                  INSTANCE IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
                  FORCE IN BOOLEAN DEFAULT FALSE);

job - An output parameter. This is the identifier assigned to the job you are creating. You must use this job number whenever you want to alter or remove the job.

what - The PL/SQL code you want to execute.

next_date - The next date when the job will be run. The default value is SYSDATE.

interval - The date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL.

no_parse - If FALSE (default), then Oracle parses the associated procedure with the job. If TRUE, then the procedure is parsed only the first time the job is run.

instance - Specifies which instance can execute the job

force - If FALSE, then the instance must already be running or else it will return an error.

Example:

VARIABLE jobno NUMBER 
BEGIN
DBMS_JOB.SUBMIT (:jobno,'HOUSEKEEP.DEL004;',SYSDATE,'SYSDATE+(59/(24*60*60))');
COMMIT;
END;
/
PRINT jobno

JOBNO
----------
5

In the above example we are scheduling a job DEL004. This is basically a procedure located in package HOUSEKEEP. The interval at which the job should be executed is 60 seconds.

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;	08/11/2002 	12:35:22	sysdate+(59/(24*60*60))	
2	HOUSEKEEP.DEL002;	08/11/2002 	12:39:45	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))	

5 rows selected.

DBMS_JOB.REMOVE

Removes a specified job from the job queue.

DBMS_JOB.REMOVE (job IN  BINARY_INTEGER );

job - The number of the job already held in the job queue.

Example:

EXECUTE DBMS_JOB.REMOVE(2); 

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;	08/11/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.