Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jan 3, 2003

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

By DatabaseJournal.com Staff

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.


Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date