Oracle Database provides advanced scheduling capabilities through Oracle Scheduler (the Scheduler). 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
, which is the package that you use to configure and operate the Scheduler.
See Also:
The documentation on supplied PL/SQL packages that came with your Oracle9i software for information on theDBMS_JOB
packageThis chapter contains the following topic:
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.
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; /
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 JOB1
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; /
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; /
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_SCHEDULER
package