Combining schedules with DBMS_SCHEDULER

Suppose I want to create job which will execute every 30 minutes in “working” hours, and at the rest of the day every hour.

For example:
8:00
8:30
9:00
9:30
..
..
18:00
19:00
20:00
…


With DBMS_JOB I would write something like this in repeat interval:

(CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'HH24')) IN (8,9,10,11,12,13,14,15,16,17) 
THEN TRUNC(SYSDATE,'HH')+30/1440 
ELSE TRUNC(sysdate,'HH')+1/24 END)

In DBMS_SCHEDULER I will create two schedules (“WORK”,”NIGHT”) and main schedule ("MAIN_DAY") which will combine those two for job schedule.

BEGIN

DBMS_SCHEDULER.create_schedule ('WORK',repeat_interval => 'FREQ=DAILY;BYHOUR=8,9,10,11,12,13,14,15,16,17;BYMINUTE=0,30');

DBMS_SCHEDULER.create_schedule ('NIGHT',repeat_interval => 'FREQ=DAILY;BYHOUR=0,1,2,3,4,5,6,7,18,19,20,21,22,23;' );

DBMS_SCHEDULER.create_schedule ('MAIN_DAY', repeat_interval =>'WORK, NIGHT');

END;

/

To test schedule I will create simple table and job which will insert sysdate into table.

create table admin.test_jobs_exec (run_date date);

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"ADMIN"."J_TEST_JOBS_EXEC"',
            schedule_name => '"ADMIN"."MAIN_DAY"',
            job_type => 'PLSQL_BLOCK',
            job_action => 'begin
                           insert into test_jobs_exec values (sysdate);
                           commit;
                           end;',
            number_of_arguments => 0,
            enabled => FALSE,
            auto_drop => FALSE
);
    
    DBMS_SCHEDULER.enable(
             name => '"ADMIN"."J_TEST_JOBS_EXEC"');
END;
/

Let’s query test_jobs_exec table to see if scheduler used wanted job schedule.

select run_date from test_jobs_exec
order by run_date;


RUN_DATE          
-------------------
...
- 15.04.2014 16:00:32 
- 15.04.2014 16:30:32 
- 15.04.2014 17:00:32 
- 15.04.2014 17:30:32 
15.04.2014 18:00:32 
15.04.2014 19:00:33 
15.04.2014 20:00:32 
15.04.2014 21:00:32 
15.04.2014 22:00:32 
15.04.2014 23:00:32 
16.04.2014 00:00:32 
16.04.2014 01:00:32 
16.04.2014 02:00:32 
16.04.2014 03:00:32 
16.04.2014 04:00:33 
16.04.2014 05:00:32 
16.04.2014 06:00:32 
16.04.2014 07:00:32 
- 16.04.2014 08:00:32 
- 16.04.2014 08:30:32 
- 16.04.2014 09:00:32 
- 16.04.2014 09:30:32 
...

If you are still using old DBMS_JOB package replace it with DBMS_SCHEDULER.
You will get better logging, simple scheduling syntax, stored/reusable schedules and many more nice features.