Oracle® Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01 |
|
|
View PDF |
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 theDBMS_JOB package |
This 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 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; /
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; /