userimage

schedular in oracle

HI,

 How can we create a schedular in oracle.

Thanks,

Radhika.G

userimage

A schedular will call the mentioned procedure every mentioned intervel of time. Like below


Procedure created like below
create or replace procedure test_none is
lv_cnt NUMBER;
begin
select max(num) into lv_cnt from TEST_DATA;
insert into TEST_DATA values(lv_cnt+1,'ABC');
END;

Schedular created like below

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_scheduler',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN test_none; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=1; bysecond=0;',
    enabled         => TRUE);
END;
/

It will call the proc every hour first minut. Like if it is compiled at 12 PM. then proc will call at 1,2 ,3...etc


Below are some examplet time intervals.
-->runs for every 3 Minuts
repeat_interval => 'FREQ=MINUTELY;INTERVAL=3',

-->Run every Friday. (All three examples are equivalent.)
FREQ=DAILY; BYDAY=FRI;
FREQ=WEEKLY; BYDAY=FRI;
FREQ=YEARLY; BYDAY=FRI;

-->Run every other Friday.
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI;

-->Run on the last day of every month.
FREQ=MONTHLY; BYMONTHDAY=-1;

-->Run on the next to last day of every month.
FREQ=MONTHLY; BYMONTHDAY=-2;

-->Run on March 10th. (Both examples are equivalent)
FREQ=YEARLY; BYMONTH=MAR; BYMONTHDAY=10;
FREQ=YEARLY; BYDATE=0310;

-->Run every 10 days.
FREQ=DAILY; INTERVAL=10;

-->Run daily at 4, 5, and 6PM.
FREQ=DAILY; BYHOUR=16,17,18;

Answer is