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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


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











×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.