Date Dimension generator Oracle PL/SQL

This PL/SQL procedure populates a date dimension table for use in dimensionally modelled data warehousing

create or replace procedure date_dim()as declare
start_year NUMBER;end_year NUMBER;last_key NUMBER;
the_date DATE;
begin
start_year := 2008;end_year := 2009; the_date := to_date('01 Jan 'start_year,'dd mon yyyy');
select max(date_key) into last_key from sla_report_presentation.date_dimension;
while (extract(year from the_date) <= end_year) LOOP last_key := last_key + 1; --DBMS_OUTPUT.PUT_LINE(to_char(the_date,'DAY d ddd dd mm MONTH yyyy')); insert into sla_report_presentation.date_dimension values (last_key ,to_char(the_date,'YYYY') ,to_char(the_date,'MONTH') ,to_char(the_date,'DD') ,to_char(the_date,'DAY') ,case when to_char(the_date,'DAY') in ('Saturday','Sunday') then 'Weekend' else 'Weekday' end ,the_date ,to_char(the_date,'DDD') ,to_char(the_date,'MM') ); the_date := the_date + interval '1' day; end loop;
commit;
end;