Say we want create a generated list of values – sequential dates, for example – as a set of rows. We can do this using recursive common table expressions.

Here’s an example of a common table expression to generate a set of dates:

with dates as
(
   select date '2012-01-01' as DATE_VALUE from dual
   union all
   select date '2012-01-02' as DATE_VALUE from dual
   union all
   select date '2012-01-03' as DATE_VALUE from dual
)
select * from dates;

Using this technique, we could create a set of rows with sequential date values… but it is not very flexible. Ideally, we want a technique that expresses the intent algorithmically.

In 11g R2 and later, we can use a reference to the outer table recursively from inside the definition! This sounds crazy but it works:

with dates as
(
   select date '2012-01-01' as DATE_VALUE from dual
   union all
   select d.DATE_VALUE + interval '1' day as DATE_VALUE
   from dates d
   where d.DATE_VALUE < date '2013-01-01'
)
select * from dates;

In this example, we are UNIONING the first row with a sub-select against itself, adding a new row that increments the date value by 1.

The first time I tried this technique, I got the following error:

ORA-32039: recursive WITH clause must have column alias list

Some research on the web gave me a page that explained what the problem was. The error message is actually very clear: we need to add a list of columns at the top of the WITH <alias> clause: 

with dates ( DATE_VALUE ) as
(
   select date '2012-01-01' as DATE_VALUE from dual
   union all
   select d.DATE_VALUE + interval '1' day as DATE_VALUE
   from dates d
   where d.DATE_VALUE < date '2013-01-01'
)
select * from dates;

And now, it just works:

SQL>
DATE_VALUE
-----------
2012-01-01
2012-01-02
2012-01-03
2012-01-04
2012-01-05
2012-01-06
2012-01-07
2012-01-08
2012-01-09
2012-01-10
2012-01-11
:

Aside: Here’s how to do the same query in PostgreSQL:

with recursive dates ( DATE_VALUE ) as
(
   select date '2012-01-01' as DATE_VALUE 
   union all
   select cast(d.DATE_VALUE + interval '1' day as date) as DATE_VALUE
   from dates d
   where d.DATE_VALUE < date '2013-01-01'
)
select * from dates;

The nice thing about this technique is that we can control the end date, and the increment size, by variables: 

define _increment = '5';
define _start     = date '2012-01-01';
define _end       = date '2012-02-01';
 
with dates ( DATE_VALUE ) as
(
   select &_start as DATE_VALUE from dual
   union all
   select d.DATE_VALUE + interval '&_increment' day as DATE_VALUE
   from dates d
   where d.DATE_VALUE < &_end
)
select * from dates;

This gives us a list of dates 5 days apart.

Using this technique in a PL/SQL block

If you’re in a PL/SQL block, trying to generate a list dynamically based on parameter values, the code will need to look slightly different: 

declare
   p_start_time date;
   p_interval_days integer;
   p_end_date date;
   --
   l_interval interval day to second;
begin
   l_interval := numtodsinterval( p_interval_days, 'day' );
   insert into My_List_Of_Dates
   with
   dates ( DATE_VALUE ) as
   (
      select
         p_start_time as DATE_VALUE
      from dual
      union all
      select
         DATE_VALUE + l_interval as DATE_VALUE
      from dates d
      where d.END_TIME < p_end_date
   )
   select DATE_VALUE from dates;
end;