/***********************************************************************/
/* Virtualisera en tabell som innehåller alla datum för ett angivet år */
/* Christer Rehnström 	Elbrus AB	2005-10-08                     */
/***********************************************************************/


/*************************************************************
Variant 1, enklast
*************************************************************/

create or replace type DateArray as table of date;
/

create or replace function get_date(p_year number) return DateArray
	PIPELINED
 is
  begin
  for i in 0 .. to_number(to_char(to_date(p_year||'-12-31','YYYY-MM-DD'),'DDD'))-1
  loop
    pipe row (to_date(p_year||'-01-01','YYYY-MM-DD')+i);
  end loop;
  return;
end;
/



select * from table( get_date(2005) );


/*****************************************************************
eller varför inte variant 2, lite krångligare men även flexiblare
*****************************************************************/


create or replace type DateScalarType as object
   (  
       YEAR_DATE    date
   )
/


create or replace type DateTableType as table of DateScalarType;
/


create or replace function get_date_table(p_year number) return DateTableType
 as
  l_data             DateTableType := DateTableType();
  begin
  for i in 0 .. to_number(to_char(to_date(p_year||'-12-31','YYYY-MM-DD'),'DDD'))-1
  loop
    l_data.extend;
    l_data(l_data.count) := DateScalarType(to_date(p_year||'-01-01','YYYY-MM-DD')+i); 
  end loop;
  return l_data;
end;
/



select  YEAR_DATE,rownum from the ( select cast( get_date_table(2005) as DatetableType ) from dual );

/*****************************************************************
eller varför inte variant 3, omständig och ganska onödig men ändå
kanske nyttig någonstans
*****************************************************************/

create or replace package YEAR_DATE
    as
        type rc is ref cursor;
    
        procedure get_date( p_cursor in out rc ,p_year number);
    end;
/

create or replace package body YEAR_DATE
as

	 procedure get_date( P_cursor in out rc ,p_year number) 
 as
  l_data             DateTableType := DateTableType();
  begin
  for i in 0 .. to_number(to_char(to_date(p_year||'-12-31','YYYY-MM-DD'),'DDD'))-1
  loop
    l_data.extend;
    l_data(l_data.count) := DateScalarType(to_date(p_year||'-01-01','YYYY-MM-DD')+i); 
  end loop;

 open p_cursor for
	select * from TABLE ( cast ( l_data as DateTableType) );

end;
end;
/


set autoprint on
variable x refcursor

exec year_date.get_date(:x,2005);