Clever and elegant way to generate complex lists of dates with DBMS_SHEDULER.EVALUATE_CALENDAR_STRING

create or replace type timestamp_table_type is table of timestamp;
/

create or replace function list_of_dates(
   calendar_string varchar2,
   start_date      timestamp with time zone,
   stop_date       timestamp with time zone)
return timestamp_table_type pipelined 
is
   l_return_date_after timestamp with time zone := start_date - interval '1' second;
   l_next_run_date     timestamp with time zone;
begin
 
   loop
   
      dbms_scheduler.evaluate_calendar_string(
         calendar_string   => calendar_string,
         start_date        => start_date,
         return_date_after => l_return_date_after,
         next_run_date     => l_next_run_date);
   
      exit when l_next_run_date > coalesce(stop_date, date '9999-12-31');
   
      pipe row(l_next_run_date);
   
      l_return_date_after := l_next_run_date;
   
   end loop;
   
end list_of_dates;
/

List of days for this year

select column_value
  from table(list_of_dates('FREQ=DAILY;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
                            trunc(sysdate, 'yyyy'),
                            add_months(trunc(sysdate, 'yyyy'), 12) - 1));
 
1    01-JAN-11 12.00.00.000000 AM
2    02-JAN-11 12.00.00.000000 AM
3    03-JAN-11 12.00.00.000000 AM
4    04-JAN-11 12.00.00.000000 AM
5    05-JAN-11 12.00.00.000000 AM
...
361  27-DEC-11 12.00.00.000000 AM
362  28-DEC-11 12.00.00.000000 AM
363  29-DEC-11 12.00.00.000000 AM
364  30-DEC-11 12.00.00.000000 AM
365  31-DEC-11 12.00.00.000000 AM

List of the last-but-one day for each month from now to the end of the year

 
select column_value
  from table(list_of_dates('FREQ=MONTHLY; BYMONTHDAY=-2;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
                            sysdate, 
                            add_months(trunc(sysdate, 'yyyy'), 12) - 1));
 
1   27-FEB-11 12.00.00.000000 AM
2   30-MAR-11 12.00.00.000000 AM
3   29-APR-11 12.00.00.000000 AM
4   30-MAY-11 12.00.00.000000 AM
5   29-JUN-11 12.00.00.000000 AM
6   30-JUL-11 12.00.00.000000 AM
7   30-AUG-11 12.00.00.000000 AM
8   29-SEP-11 12.00.00.000000 AM
9   30-OCT-11 12.00.00.000000 AM
10  29-NOV-11 12.00.00.000000 AM
11  30-DEC-11 12.00.00.000000 AM

List of 1am, 3am, 7am at 1pm every day from the current day to 9999-12-31

select column_value
  from table(list_of_dates('FREQ=DAILY; BYHOUR=1,4,7,13;BYMINUTE=0;BYSECOND=0',
                            trunc(sysdate), 
                            date '9999-12-31'));
 
1  04-FEB-11 01.00.00.000000 AM
2  04-FEB-11 04.00.00.000000 AM
3  04-FEB-11 07.00.00.000000 AM
4  04-FEB-11 01.00.00.000000 PM
5  05-FEB-11 01.00.00.000000 AM
6  05-FEB-11 04.00.00.000000 AM
7  05-FEB-11 07.00.00.000000 AM
8  05-FEB-11 01.00.00.000000 PM
...

List of first day of each calendar quarter from the current day to 9999-12-31

 
select column_value
  from table(list_of_dates('FREQ=YEARLY;BYDATE=0101,0401,0701,1001;BYHOUR=0;BYMINUTE=0;BYSECOND=0',
                            trunc(sysdate), 
                            date '9999-12-31'));
                            
1  01-APR-11 12.00.00.000000 AM
2  01-JUL-11 12.00.00.000000 AM
3  01-OCT-11 12.00.00.000000 AM
4  01-JAN-12 12.00.00.000000 AM
5  01-APR-12 12.00.00.000000 AM
6  01-JUL-12 12.00.00.000000 AM
...

List of every Monday, Wednesday and Friday at twelve and thirty seconds from the current day to 9999-12-31

select column_value
  from table(list_of_dates('FREQ=DAILY;BYDAY=MON,WED,FRI;BYHOUR=12;BYMINUTE=0;BYSECOND=30',
                            trunc(sysdate), 
                            date '9999-12-31'));
                            
1  04-FEB-11 12.00.30.000000 PM
2  07-FEB-11 12.00.30.000000 PM
3  09-FEB-11 12.00.30.000000 PM
4  11-FEB-11 12.00.30.000000 PM
5  14-FEB-11 12.00.30.000000 PM
6  16-FEB-11 12.00.30.000000 PM
...

From: oraclesponge.wordpress.com