Switch From DBMS_JOB to DBMS_SCHEDULAR

The previous database scheduler was DBMS_JOB. Now oracle database offers much more flexibility with the package DBMS_SCHEDULAR and typically it replaces DBMS_JOB package with enhance flexibility and comes with many more options.

In this post I will show you briefly how you can take statements created with DBMS_JOB and rewrite them using DBMS_SCHEDULER, which is the package that you use to configure and operate the Scheduler.

A)Creating a JOB:
----------------------
An example of creating a job using DBMS_JOB is the following:
SQL> desc a;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B VARCHAR2(10)


VARIABLE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'INSERT INTO a VALUES (1,''First'');',SYSDATE, 'SYSDATE+1/24/60');
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 a values(1,''First'');',
start_date =>SYSDATE,
repeat_interval => 'FREQ =MINUTELY; INTERVAL = 1');
END;
/


B)Altering a Job:
-------------------------------------------
Using DBMS_JOB,

BEGIN
DBMS_JOB.WHAT(23, 'INSERT INTO a VALUES (2,''Second'');');
COMMIT;
END;
/
The jobid can be selected from query dba_jobs.

Using DBMS_SCHEDULAR,

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'JOB1',
attribute => 'job_action',
value => 'INSERT INTO a values(2,''Second'');');
END;
/

C)Removing a Job from the Job Queue
----------------------------------------------------

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

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


Using DBMS_SCHEDULER, you would issue the following statement instead:

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