oracle-databaseplsqlscheduled-tasksdbms-scheduler

Oracle DBMS_Scheduler RUN DBMS_JOB on 9:10AM and 11:30 AM Daily


I want to schedule a job which should run 09:10AM and 11:45AM on every Saturday.

I am trying to create a job dbms_scheduler.create_job with following interval

,repeat_interval => 'FREQ=WEEKLY; BYDAY=THU; BYHOUR=9,11; BYMINUTE=10,45; BYSECOND=0'

but looks like its creating 4 jobs which will run every Thursday at 9 and 11 hours on 30 and 45 minutes.

Is there any way to create the job interval which will run the job on 9:10 and 11:45 on every Saturday?


Solution

  • Create two schedules.

    BEGIN
      dbms_scheduler.create_schedule (
        schedule_name   => 'SCHEDULE_9', 
        repeat_interval => 'FREQ=WEEKLY; BYDAY=SAT; BYHOUR=9;  BYMINUTE=10; BYSECOND=00;');
      dbms_scheduler.create_schedule (
        schedule_name   => 'SCHEDULE_11', 
        repeat_interval => 'FREQ=WEEKLY; BYDAY=SAT; BYHOUR=11; BYMINUTE=45; BYSECOND=00;'); 
      dbms_scheduler.create_job (
        job_name        => 'MY_JOB',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'MY_PROCEDURE',
        start_date      => systimestamp,
        repeat_interval => 'SCHEDULE_9,SCHEDULE_11',
        end_date        => NULL,
        enabled         => TRUE,
        comments        => 'Job that runs at 09:10 and 11:45 every Saturday ');
    END;
    /