Just a quick follow up to my post from the other day about using DBMS_Scheduler to generate lists of dates in SQL.
I mentioned that you can use named schedules in this method, and that’s actually a handy way of testing your schedule definition.
The syntax is along the lines of:
begin dbms_scheduler.create_schedule( schedule_name => 'DAILY_4AM', repeat_interval => 'FREQ=DAILY;BYHOUR=4;BYMINUTE=0;BYSECOND=0'); end; / select * from table( list_of_dates( 'daily_4am', sysdate , null)); 2010-08-21 04.00.00.000000000 2010-08-22 04.00.00.000000000 2010-08-23 04.00.00.000000000 2010-08-24 04.00.00.000000000 2010-08-25 04.00.00.000000000 2010-08-26 04.00.00.000000000 2010-08-27 04.00.00.000000000 2010-08-28 04.00.00.000000000 2010-08-29 04.00.00.000000000 ...
Another interesting syntax, which of course is inherited directly from the DBMS_Scheduler calendaring syntax, allows named schedules to be combined in interesting ways that broadly correspond to the SQL set operators UNION, INTERSECT and MINUS by specifying INCLUDE, INTERSECT and EXCLUDE clauses (note the missed opportunity to use the same key words there, rather a shame I think).
So this would allow you to specify:
select * from table( list_of_dates( 'WEEKDAYS;EXCLUDE=PUBLIC_HOLIDAYS', sysdate , null));select * from table( list_of_dates( 'WEEKENDS,PUBLIC_HOLIDAYS', sysdate , null));
INCLUDE is a funny one — the syntax diagrams imply that you can “union” named schedules by listing them, as in the example above, or by using the INTERSECT clause. INTERSECT is really used for combining a regular schedule such as “FREQ=DAILY;BYHOUR=3;BYMIN=30;BYSECOND=0′ with a named schedule.
This prompts the thought of whether it is better to specify a regular calendar string in the SQL when querying for a list of dates, or to define a named schedule and reference that. I suspect that named schedules would really be of value for defining a complex list such as “US_PUBLIC_HOLIDAYS” or “UK_PUBLIC_HOLIDAYS” that you want to keep as a standard reference.
I might be over-thinking this.
Mind you, this listing of dates would be a handy feature in SQL Developer’s enhancement for DBMS_Scheduler I suppose.