Moving from DBMS\_JOB to Oracle Scheduler

This section illustrates some examples of how you can take jobs created with the DBMS_JOB package and rewrite them using Oracle Scheduler, which you configure and control with the DBMS_SCHEDULER package.

• Creating a Job
  An example illustrates creating a job using the DBMS_JOB package and the DBMS_SCHEDULER package.
• Altering a Job
  An example illustrates altering a job using the DBMS_JOB package and the DBMS_SCHEDULER package.
• Removing a Job from the Job Queue
  An example illustrates removing a job using the DBMS_JOB package and the DBMS_SCHEDULER package.

Parent topic: Support for DBMS_JOB

A.2.1 Creating a Job

An example illustrates creating a job using the DBMS_JOB package and the DBMS_SCHEDULER package.

The following example creates a job using DBMS_JOB:

VARIABLE jobno NUMBER;
BEGIN
 DBMS\_JOB.SUBMIT(:jobno, 'INSERT INTO employees VALUES (7935, ''SALLY'',
   ''DOGAN'', ''sally.dogan@examplecorp.com'', NULL, SYSDATE, ''AD\_PRES'', NULL, 
    NULL, NULL, NULL);', SYSDATE, 'SYSDATE+1');
 COMMIT;
END;
/

The following is an equivalent statement using DBMS_SCHEDULER:

BEGIN
 DBMS\_SCHEDULER.CREATE\_JOB(
   job\_name          =>  'job1',
   job\_type          =>  'PLSQL\_BLOCK',
   job\_action        =>  'INSERT INTO employees VALUES (7935, ''SALLY'',
     ''DOGAN'', ''sally.dogan@examplecorp.com'', NULL, SYSDATE,''AD\_PRES'', NULL,
      NULL, NULL, NULL);',
   start\_date        =>  SYSDATE,
   repeat\_interval   =>  'FREQ = DAILY; INTERVAL = 1');
END;
/

Parent topic: Moving from DBMS_JOB to Oracle Scheduler

A.2.2 Altering a Job

An example illustrates altering a job using the DBMS_JOB package and the DBMS_SCHEDULER package.

The following example alters a job using DBMS_JOB:

BEGIN
 DBMS\_JOB.WHAT(31, 'INSERT INTO employees VALUES (7935, ''TOM'', ''DOGAN'', 
   ''tom.dogan@examplecorp.com'', NULL, SYSDATE,''AD\_PRES'', NULL,
   NULL, NULL, NULL);');
 COMMIT;
END;
/

This changes the action for JOB1 to insert a different value.

The following is an equivalent statement using DBMS_SCHEDULER:

BEGIN
 DBMS\_SCHEDULER.SET\_ATTRIBUTE(
   name          => 'JOB1',
   attribute     => 'job\_action',
   value         => 'INSERT INTO employees VALUES (7935, ''TOM'', ''DOGAN'', 
      ''tom.dogan@examplecorp.com'', NULL, SYSDATE, ''AD\_PRES'', NULL,
      NULL, NULL, NULL);');
END;
/

Parent topic: Moving from DBMS_JOB to Oracle Scheduler

A.2.3 Removing a Job from the Job Queue

An example illustrates removing a job using the DBMS_JOB package and the DBMS_SCHEDULER package.

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;
/