Listing DBMS_Scheduler Named-Calendar Dates with SQL

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.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s