René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback

Range of dates with PL/SQL

create or replace type date_table as table of date

create or replace function date_range(from_dt in date, to_dt in date) 
  return date_table as
    a_date_table date_table := date_table();
    cur_dt date:=from_dt;
    while cur_dt <= to_dt loop
      a_date_table(a_date_table.count) := cur_dt;
      cur_dt := cur_dt + 1;
    end loop;
  return a_date_table;
end date_range;

create table t_history (dt date, i number);

insert into t_history values(to_date('10.03.2003',''),5);
insert into t_history values(to_date('13.03.2003',''),3);
insert into t_history values(to_date('15.03.2003',''),1);

select dt_range.column_value, case when i is null then 0 else i end from table (
  cast ( date_range(
    ) as date_table)
  ) dt_range left join t_history t on dt_range.column_value = t.dt
    order by

drop table t_history;
drop type date_table;