An Oracle9i Pipelined Table Function : DBMS_PIPE « System Packages « Oracle PL / SQL






  1. Oracle PL / SQL
  2. System Packages
  3. DBMS_PIPE

An Oracle9i Pipelined Table Function

    
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>








Related examples in the same category

1.DBMS_PIPE.PACK_MESSAGE
2.DBMS_PIPE.UNPACK_MESSAGE
3.dbms_pipe.remove_pipe
4.Use DBMS_PIPE package to receive a message.
5.Use DBMS_PIPE package to send a message.
6.Use DBMS_PIPE.PACK_MESSAGE in a trigger
7.Define an anonymous block to populate the local private pipe.
8.An anonymous block program to create a pipe.
9.An anonymous block program to delete a pipe
10.Run a DBMS_PIPE.RECEIVE_MESSAGE call to empty the local buffer
11.This script deletes a pipe if it exists in the context of the current session, then recreates it.
12.This script unpacks the local buffer.