Skip Headers

Oracle® Database Administrator's Guide
10g Release 1 (10.1)

Part Number B10739-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

25 Moving from DBMS_JOB to DBMS_SCHEDULER

Oracle Database provides advanced scheduling capabilities through the database Scheduler, a collection of functions and procedures in the DBMS_SCHEDULER package. The Scheduler offers far more functionality than the DBMS_JOB package, which was the previous Oracle Database job scheduler. This chapter discusses briefly how you can take statements created with DBMS_JOB and rewrite them using DBMS_SCHEDULER.


See Also:

The documentation on supplied PL/SQL packages that came with your Oracle9i software for information on the DBMS_JOB package

This chapter contains the following topic:

Moving from DBMS_JOB to DBMS_SCHEDULER

This section illustrates some examples of how you can take jobs created with the DBMS_JOB package and rewrite them using the DBMS_SCHEDULER package.

Creating a Job

An example of creating a job using DBMS_JOB is the following:

VARIABLE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'INSERT INTO employees VALUES (7935, ''SALLY'',
   ''DOGAN'', ''sally.dogan@xyzcorp.com'', NULL, SYSDATE, ''AD_PRES'', NULL, 
    NULL, NULL, NULL);', SYSDATE, 'SYSDATE+1');
COMMIT;
END;
/

An equivalent statement using DBMS_SCHEDULER is the following:

BEGIN
DBMS_SCHEDULER.CREATE_JOB(
   job_name          =>  'job1',
   job_type          =>  'PLSQL_BLOCK',
   job_action        =>  'INSERT INTO employees VALUES (7935, ''SALLY'',
     ''DOGAN'', ''sally.dogan@xyzcorp.com'', NULL, SYSDATE,''AD_PRES'', NULL,
      NULL, NULL, NULL);');
   start_date        =>  SYSDATE,
   repeat_interval   =>  'FREQ = DAILY; INTERVAL = 1');
END;
/

Altering a Job

An example of altering a job using DBMS_JOB is the following:

BEGIN
DBMS_JOB.WHAT(31, 'INSERT INTO employees VALUES (7935, ''TOM'', ''DOGAN'', 
   ''tom.dogan@xyzcorp.com'', NULL, SYSDATE,''AD_PRES'', NULL,
   NULL, NULL, NULL);');
COMMIT;
END;
/

This changes the action for job 31 to insert a different value. An equivalent statement using DBMS_SCHEDULER is the following:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
   name          => 'JOB1',
   attribute     => 'job_action',
   value         => 'INSERT INTO employees VALUES (7935, ''TOM'', ''DOGAN'', 
      ''tom.dogan@xyzcorp.com'', NULL, SYSDATE, ''AD_PRES'', NULL,
      NULL, NULL, NULL);');
END;
/

Removing a Job from the Job Queue

The following example removes a job using DBMS_JOB, where 14144 is the number of the job being run:

BEGIN
DBMS_JOB.REMOVE(14144);
COMMIT;
END;
/

Using DBMS_SCHEDULER, you would issue the following statement instead:

BEGIN
   DBMS_SCHEDULER.DROP_JOB('myjob1');
END;
/