oracle-databaseintervalsdbms-job

How to Submit dbms_job to begin at 06:00 and run every hour, Monday


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.


Solution

  • 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;