[SOLVED] Oracle DBMS Scheduler change in frequency on weekends

Oracle DBMS Scheduler change in frequency on weekends

I have created a oracle dbms scheduler to execute a procedure daily at 05 AM, 10 AM, 03 PM and 08 PM. Below is the scheduler code

DBMS_SCHEDULER.CREATE_JOB
(
   job_name        => 'TEST_JOB'
  ,start_date      => SYSDATE
  ,repeat_interval => 'FREQ=DAILY; BYHOUR=05,10,15,20; BYMINUTE=00 ;BYSECOND=0;'
  ,end_date        => NULL
  ,job_class       => 'DEFAULT_JOB_CLASS'
  ,job_type        => 'PLSQL_BLOCK'
  ,enabled         => TRUE
  ,job_action      => 'BEGIN INSERT_IN_TABLE; END;'
  ,comments        => 'TEST JOB'
);

now i have to modify the same scheduler to execute the same procedure only twice on weekends and run at same frequency on weekdays.

I don't want to create a different scheduler for the weekend executions because sometimes the procedure takes more than 5 hours to execute.

Please guide me if there is a better way to achieve this.

Solution

One option could be to use embedded calendars, so that you can create your own calendar expression.

Let me show you an example

SQL> BEGIN
dbms_scheduler.create_schedule('my_schedule_c_1', repeat_interval =>
  'FREQ=DAILY; BYHOUR=05,10,15,20; BYMINUTE=00; BYSECOND=00; ');
dbms_scheduler.create_schedule('my_schedule_c_2', repeat_interval =>
  'FREQ=DAILY; BYDAY=SAT,SUN; BYHOUR=05,10; BYMINUTE=00; BYSECOND=00;');
END;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

SQL> begin
   DBMS_SCHEDULER.create_schedule ('MY_CALC', repeat_interval =>'my_schedule_c_1, my_schedule_c_2');
END;
/  2    3    4

PL/SQL procedure successfully completed.

SQL>

Then , you only need to apply this schedule to your job

SQL> begin
  2  DBMS_SCHEDULER.CREATE_JOB
(
   job_name        => 'TEST_JOB'
  ,start_date      => SYSDATE
  3    ,repeat_interval => 'MY_CALC'
  4    ,end_date        => NULL
  ,job_class       => 'DEFAULT_JOB_CLASS'
  ,job_type        => 'PLSQL_BLOCK'
  5    6    7    8    9   10    ,enabled         => TRUE
  ,job_action      => 'BEGIN NULL; END;'
  ,comments        => 'TEST JOB'
); 11   12   13
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL>

This way, my job will run using the MAIN_CALC schedule, which is a combination of the two different frequencies.

Of course, you can always create two jobs, but in 11g there is no option to create incompatibilities, which is an object in DBMS_SCHEDULER 12c onwards that prevents a job to start until the other is completed.

My advice, use a schedule calendar embedded with multiple frequencies