pipelined returning value and table() function

   

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.

Related examples in the same category

1.	An example of a pipelined table function.
2.	Using a pipelined table function.

pipelined returning value and table() function : PIPELINED « Stored Procedure Function « Oracle PL / SQL

Related examples in the same category