DBMS_SCHEDLUER ( ORACLE 10G)

STEP 1: Create a Procedure which you want to schedule.

CREATE OR REPLACE PROCEDURE PROC1
AS
BEGIN
INSERT INTO TEMP (ANI) VALUES(9856678955);
END;


STEP 2 :Schedule a Procedure (Job) using DBMS_SCHEDULER.

BEGIN
dbms_scheduler.create_job( job_name=>'JOB1',
job_type=>'STORED_PROCEDURE',
job_action=>'SCOTT.PROC1',
number_of_arguments=>0,
start_date=>TRUNC(SYSDATE),
repeat_interval=>'FREQ=MINUTELY;INTERVAL=1',
job_class=> 'DEFAULT_JOB_CLASS',
enabled=>TRUE,
auto_drop=>FALSE,
comments=>NULL);
END;
/



DROPPING A JOB:If you want to drop a existing job:

exec dbms_scheduler.drop_job(’SCOTT.JOB1’);

Some example of parameter repeat_interval:

1. repeat_interval=>’FREQ=daily;byhour=20;byminute=0;bysecond=0′.
The job will runs daily from Monday to Sunday at 20:00 hours.
2. repeat_interval=>’FREQ=daily;byhour=20;byminute=30;bysecond=0′.
The job will runs daily from Monday to Sunday at 20:30 hours.
3. repeat_interval=>’FREQ=monthly;bymonthday=10,20; byminute=0;bysecond=0;’
The job will on 10th and 20th day of each month.
4. repeat_interval=>’FREQ=yearly;bymonth=JAN,APR,SEP,DEC;BYMONTHDAY=21;
byhour=20;byminute=30;bysecond=0′ The job will run on 21st of months
JAN, APR, SEP and DEC at 20:30 hours.

UPDATING A SCHEDULED JOB

DBMS_SCHEDULER.SET_ATTRIBUTE (
name=>’SCOTT.JOB2’,
attribute=>’job_action’,
value=>’SCOTT.PROC2’);