SQL>
SQL> create or replace type dateTableType as table of date;
2 /
Type created.
SQL>
SQL> create or replace function month_generator_piped(p_num_months in number)
2 RETURN dateTableType
3 PIPELINED
4 AS
5 month_table dateTableType := dateTableType();
6 BEGIN
7 for i in 1..p_num_months loop
8 PIPE ROW ( add_months(sysdate, -i) );
9 end loop;
10 return;
11
12 END;
13 /
Function created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> select * from TABLE( CAST (month_generator_piped(12) AS dateTableType) );
COLUMN_VA
---------
26-SEP-09
26-AUG-09
26-JUL-09
26-JUN-09
26-MAY-09
26-APR-09
26-MAR-09
26-FEB-09
26-JAN-09
26-DEC-08
26-NOV-08
26-OCT-08
12 rows selected.
SQL>
SQL> select to_Char(x.column_value, 'mm/yyyy')
2 from TABLE( month_generator_piped(12) ) x
3 group by to_Char(x.column_value, 'mm/yyyy') ;
TO_CHAR
-------
01/2009
02/2009
03/2009
04/2009
05/2009
06/2009
07/2009
08/2009
09/2009
10/2008
11/2008
12/2008
12 rows selected.
SQL>
SQL>
SQL>
|