Explode range of dates

Requires Oracle 9i and above

create or replace type dates_t as table of DATE;
/


create or replace function date_range_from_cursor ( p_cursor in sys_refcursor )
return dates_t
pipelined
as
    l_b DATE;
    l_e DATE;
    v_date DATE := NULL;
begin
    loop
        fetch p_cursor into l_b, l_e;
        exit when p_cursor%notfound;
        v_date := l_b;
        WHILE v_date <= l_e
        LOOP
          BEGIN
            pipe row(v_date);
            v_date := v_date + 1;
          END;
        END LOOP;
    end loop;
    close p_cursor;
    return;
end;
/


select *
from table
  (date_range_from_cursor(cursor(select sysdate, SYSDATE + 10 from dual
  UNION
  SELECT SYSDATE + 13, SYSDATE + 20 FROM dual)))
/
COLUMN_VA
---------
20-OCT-04
21-OCT-04
22-OCT-04
23-OCT-04
24-OCT-04
25-OCT-04
26-OCT-04
27-OCT-04
28-OCT-04
29-OCT-04
30-OCT-04
02-NOV-04
03-NOV-04
04-NOV-04
05-NOV-04
06-NOV-04
07-NOV-04
08-NOV-04
09-NOV-04

Links

• Subscribe to this feed