Minnu's Blog on Informatica & Data warehouse concepts


Wednesday, April 11, 2007

Time dimension stored procedure script

/* Expecting that you have a table called time with following columns, & you are working on oracle SQL*Plus

Curr_date Date
Day_of_the_month varchar2(5)
Month varchar2(20)
Year varchar2 (7)
Quarter Varchar2(5)
Day_of_the_week varchar2(15)
Holiday Varchar2(5)

Now copy & save the following script with .sql , Compile & then execute. Remember to change the date in the script to watever date you want to start from.

@min2 --> Compilation

Exec min2 -> Execution


create or replace procedure min2


olddate date;
systdate date;
sysd date;
day varchar2(5);
mon varchar2(20);
qtr varchar2(5);
year varchar2(7);
Dweek varchar2(15);
Holiday varchar2(5);


olddate := to_date('26-MAR-1984','DD-MON-YYYY');

select sysdate into sysd from dual;

systdate := to_date(sysd,'DD-MON-YY');

While olddate < systdate loop

Select to_char(olddate,'DD') into day from dual;
Select to_char(olddate,'MON') into mon from dual;
Select to_char(olddate,'YYYY') into year from dual;
Select to_char(olddate,'Dy') into Dweek from dual;

If mon IN ('APR','MAY','JUN') then

qtr := 'Q1';

elsif mon IN ('JUL', 'AUG', 'SEP') then

qtr := 'Q2';

elsif mon IN ('OCT', 'NOV', 'DEC') then

qtr := 'Q3';

elsif mon IN ('JAN', 'FEB', 'MAR') then

qtr := 'Q4';

end if;

If Dweek IN ('Sat', 'Sun') then

Holiday := 'Yes';


Holiday := 'No';

end if;

Insert into time (curr_date, Day_of_the_month, Day_of_the_week, Holiday, Month, quarter, Year) values (olddate, day, Dweek, Holiday, mon, qtr, year);

olddate := olddate + 1;

end loop;