create or replace package body gen_date_dim
is

function month_range( range_from date, range_to date) return date_tab
is 
 p_date_tab  date_tab;
 
begin
 
 select add_months(range_from, level-1)
 bulk collect into p_date_tab
 from dual
 connect by level <= months_between(range_to,range_from);
 
 return p_date_tab;
 
end month_range;
  
end gen_date_dim;

create type date_tab is table of date

create or replace force  view dwh.v_dim_date
as
select column_value period_sid,
to_char(column_value,'YYYYMM') month_code,
to_char(column_value,'Mon YYYY') month_name,
'QTR'||to_char(column_value,'Q YYYY') quarter_name
from table(dwh.gen_date_dim.month_Range(to_date('01-jan-1900','dd-mon-yyyy'),to_date('01-dec-2100','dd-mon-yyyy')))

select column_value period_sid,
to_char(column_value,'YYYYMM') month_code,
to_char(column_value,'Mon YYYY') month_name,
'QTR'||to_char(column_value,'Q YYYY') quarter_name
from table(dwh.gen_date_dim.month_Range(to_date('01-jan-1900','dd-mon-yyyy'),to_date('01-dec-2100','dd-mon-yyyy')))