[Skip to Content]
Copyright © 2011 Marcin Jankowski. Original version is available at http://www.marcin.jankowski.com/oracle/pipelined-function-example.html

Pipelined Function Example

by Marcin Jankowski
02nd February 2011 AD
Version 1.1

Table of Contents

Overview

Pipelined functions operate like a table. It means that they can be used as the source of data instead of the regular table or view.

Their main use is whenever regular table operations are required on set of data that is either dynamically created or heavily processed.

Example

This is example of using pipelined function. This particular one is not extremely brilliant or useful but serves the demonstration purposes.

What it does is to list the Weekdays and dates in the next 10 days omitting the weekday passed as parameter.

01 Create Or Replace Package Pipelined_Example As
02
03     Type Some_Strange_Name_R Is Record(
04         Sample_Name_1      Varchar2(10),
05         Sample_Name_2      Date
06     );
07 
08     Type Some_Strange_Name_T Is Table Of Some_Strange_Name_R;
09 
10     Function Get_Data(
11         P_Some_Parameter_I   Varchar
12     ) Return Some_Strange_Name_T Pipelined;
13 
14 End Pipelined_Example;
15 /
16 
17 Create Or Replace Package Body Pipelined_Example As
18 
19     Function Get_Data(
20         P_Some_Parameter_I   Varchar
21     ) Return Some_Strange_Name_T Pipelined Is
22         Local_Record    Some_Strange_Name_R;
23         Some_Exception  Exception;
24     Begin
25 
26         For K In 1..10 Loop
27 
28             Local_Record.Sample_Name_1 := Trim(To_Char(Sysdate+K,'Day'));
29             Local_Record.Sample_Name_2 := Sysdate+K;
30 
31             If ( Upper(Local_Record.Sample_Name_1) != Upper(P_Some_Parameter_I) ) Then
32                 Pipe Row (Local_Record);
33             End If;
34         End Loop;
35 
36         Return;
37     Exception
38         When Some_Exception Then
39             -- Do something
40             Return;
41     End Get_Data;
42 
43 End Pipelined_Example;
44 /

The above function can be used now as the source of data.

Query:
 
Select * From Table(Pipelined_Example.Get_Data('Thursday')) Order By Sample_Name_1 Asc;
Result:
 
Sample_Name_1   Sample_Name_2
-----------------------------
Friday          04-FEB-11
Friday          11-FEB-11
Monday          07-FEB-11
Saturday        12-FEB-11
Saturday        05-FEB-11
Sunday          06-FEB-11
Tuesday         08-FEB-11
Wednesday       09-FEB-11

Important lines in the example

Line 01:
Creating package specification for "Pipelined_Example" package

Lines 03-06:
Definition of the type that will be returned as table. Think about it as structure of the table. The name of the type does not really matter.

Line 08:
Definition of the collection type. It is table of the previous record type.

Line 12:
Specification of the function. Please not that the function returns the collection type and has the keyword pipelined at the end.

Line 22:
Declaration of local variable. In this example it is the record type defined in the specification

Line 32:
This is one of important bits. Here the local record is pipelined.

Line 36:
At the logical end of function simple return is used. There is no need to return specific variable.

Line 38:
Dummy exception

Line 40:
It is important that in exception there is also return command.