For create a job in DBMS Scheduler, exact method is first create a schedule, then create a program and then a job.
- Create a schedule
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE (
Schedule_name => 'DAILYSALESJOB_SCHED',
Start_date => SYSTIMESTAMP,
Repeat_interval =>'FREQ=DAILY;BYHOUR=11; BYMINUTE=30',
Comments => 'DAILY SALES JOB'
);
END;
/
- Create a program
In this we are calling a stored procedure as an example, you can call anything thing pl/sql block, stored procedure, function etc.
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'DAILYSALESJOB',
program_type => 'STORED_PROCEDURE',
program_action => 'SCOTT.SALES_PROC'
number_of_arguments =>0,
enabled => TRUE,
comments => 'DAILY SALES JOB'
);
END;
/
- Create the job in DBMS Scheduler.
Begin
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'DAILYSALESJOB_START',
program_name => 'DAILYSALESJOB',
schedule_name => 'DAILYSALESJOB_SCHED',
enabled => FLASE,
comments => 'daily sales job'
); END;
/
- ENABLE THE JOB.
EXEC DBMS_SCHEDULER.ENABLE('DAILYSALESJOB_START');
Drop the DBMS Scheduler job
DROP the Scheduler with DBMS Scheduler.
BEGIN
DBMS_SCHEDULER.DROP_SCHEDULE(
schedule_name => 'DAILYSALESJOB_SCHED',
force => TRUE
); END;
/
Drop the program.
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM ( program_name => 'DAILYSALESJOB');
End;
/
Drop the job in dbms scheduler.
Exec DBMS_SCHEDULER.DROP_JOB (job_name => 'DAILYSALESJOB_START');