SQL>
SQL>
SQL> create table myTable as
2 select trunc(created) created
3 from all_objects;
Table created.
SQL>
SQL> create or replace
2 type date_list is table of Date;
3 /
Type created.
SQL> create or replace function pipe_date(p_start date, p_limit number)
2 return date_list pipelined is
3 begin
4 for i in 0 .. p_limit-1 loop
5 pipe row (p_start + i);
6 end loop;
7 return;
8 end;
9 /
Function created.
SQL>
SQL> select column_value, count(created) no_of_obj
2 from myTable, table(pipe_date(trunc(sysdate)-14,14))
3 where column_value = myTable.created(+)
4 group by column_value
5 /
COLUMN_VA NO_OF_OBJ
--------- ----------
12-OCT-09 0
13-OCT-09 0
14-OCT-09 0
15-OCT-09 45
16-OCT-09 55
17-OCT-09 243
18-OCT-09 177
19-OCT-09 53
20-OCT-09 0
21-OCT-09 24
22-OCT-09 0
COLUMN_VA NO_OF_OBJ
--------- ----------
23-OCT-09 0
24-OCT-09 0
25-OCT-09 0
14 rows selected.
SQL>
SQL> drop table myTable;
Table dropped.