How to populate calendar table in Oracle?
This is a simple and easy way to do it
with calendar as (
select :startdate + rownum - 1 as day
from dual
connect by rownum < :enddate - :startdate
)
select rownum as "S.No", to_date(day,'dd_mm_yyyy') as "Cal_Dt", to_char(day,'day') as "DayName"
from calendar
with calendar as (
select rownum - 1 as daynum
from dual
connect by rownum < sysdate - to_date('1-jan-2010') + 1
)
select to_date('1-jan-2010') + daynum as monthdate
from calendar
;
declare
v_date date := to_date('20110101','yyyymmdd');
begin
while v_date < sysdate + 720 loop
insert into calender
values ( v_date, to_char(v_date,'DAY'));
v_date := v_date + 1;
end loop;
commit;
end;
/
This is not best practice and you should use Allesandro Rossi's solution. This may only be useful if you're using Oracle 9i or earlier and populating a large table.