how can i set interval for dbms_job that to begin at 06:00 and run every hour, Monday.
X NUMBER:=1102745;
BEGIN
SYS.DBMS_JOB.INTERVAL
(X,
'next_day(SYSDATE,''MONDAY'')+1/24'
);
END;
But this code working every on monday 01:00.
You can use modern SCHEDULER JOB
instead of old (and deprecated) DBMS_JOB
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'X'
,start_date => SYSTIMESTAMP
,repeat_interval => 'FREQ=HOURLY;BYHOUR=06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23;BYMINUTE=0;BYSECOND=0;BYDAY=Mon'
,end_date => NULL
,job_class => 'DEFAULT_JOB_CLASS'
,job_type => ...
,job_action => ...
);
You can verify the repeat interval with this:
DECLARE
next_run_date TIMESTAMP;
BEGIN
FOR i IN 1..50 LOOP
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=HOURLY;BYHOUR=06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23;BYMINUTE=0;BYSECOND=0;BYDAY=Mon', NULL, next_run_date, next_run_date);
DBMS_OUTPUT.PUT_LINE ( next_run_date );
END LOOP;
END;