Creating a Job
An example of creating a job using DBMS_JOB is the following:
VARIABLE jobno NUMBER;
DBMS_JOB.SUBMIT(:jobno, ‘INSERT INTO employees VALUES (1234, NEWS,
NEWS, firstname.lastname@example.org, NULL, SYSDATE, AD_PRES, NULL,
NULL, NULL, NULL);’, SYSDATE, ‘SYSDATE+1’);
An equivalent statement using DBMS_SCHEDULER is the following:
job_name => ‘job1’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘INSERT INTO employees VALUES (1234, NEWS,
NEWS, email@example.com, NULL, SYSDATE,AD_PRES, NULL,
NULL, NULL, NULL);’);
start_date => SYSDATE,
repeat_interval => ‘FREQ = DAILY; INTERVAL = 1’);
repeat_interval => ‘FREQ=HOURLY; INTERVAL=4’,
comments => ‘Every 4 hours’);
Run every Friday. (All three examples are equivalent.)
Run every other Friday.
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;
Run on the last day of every month.
Run on the next to last day of every month.
Run on March 10th. (Both examples are equivalent)
FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;
Run every 10 days.
Run daily at 4, 5, and 6PM.
Run on the 15th day of every other month.
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15;
Run on the 29th day of every month.
Run on the second Wednesday of each month.
Run on the last Friday of the year.
Run every 50 hours.
Run on the last day of every other month.
FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=-1;
Run hourly for the first three days of every month.
Here are some more complex repeat intervals:
Run on the last workday of every month (assuming that workdays are Monday through Friday).
FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1
Run on the last workday of every month, excluding company holidays. (This example references an existing named schedule called Company_Holidays.)
FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; EXCLUDE=Company_Holidays; BYSETPOS=-1
Run at noon every Friday and on company holidays.
A repeat interval of “FREQ=MINUTELY;INTERVAL=2;BYHOUR=17; BYMINUTE=2,4,5,50,51,7;” with a start date of 28-FEB-2004 23:00:00 will generate the following schedule:
SUN 29-FEB-2004 17:02:00
SUN 29-FEB-2004 17:04:00
SUN 29-FEB-2004 17:50:00
MON 01-MAR-2004 17:02:00
MON 01-MAR-2004 17:04:00
MON 01-MAR-2004 17:50:00