oracle pipelined table functions


Problem: How to create pipelined table functions in oracle

Solution: 

Table functions are used to return PL/SQL collections that mimic tables. They can be queried like a regular table by using the TABLE function in the FROM clause. Regular table functions require collections to be fully populated before they are returned. Since collections are held in memory, this can be a problem as large collections can waste a lot of memory and take a long time to return the first row. These potential bottlenecks make regular table functions unsuitable for large Extraction Transformation Load (ETL) operations. Regular table functions require named row and table types to be created as database objects.

Typically pipelined functions are useful if there is a need for a data source other than a table in a select statement as shown below

SELECT * FROM TABLE(PACKAGE_NAME.PIPELINED_FUNC_NAME)

Lets see how to create a pipelined function 

1. If you are defining these in a package, below definitions goes into the spec of the package, first thing is to specify the columns that are returned along with the type of the columns

create or replace PACKAGE package_name 

AS 

TYPE pipelinedDataRecord IS RECORD (id varchar2(100),firstname CUSTOMER.FIRST_NAME%type);

END package_name

The above definition declares the format of the rows that are returned

2. Add the below collection type for the above defined type

TYPE pipelinedDataRecordCol IS TABLE OF pipelinedDataRecord;

So the entire package declaration looks like 

 

create or replace PACKAGE package_name 

AS 

TYPE pipelinedDataRecord IS RECORD (id varchar2(100),firstname CUSTOMER.FIRST_NAME%type);

TYPE pipelinedDataRecordCol IS TABLE OF pipelinedDataRecord;

END package_name

 

3. Now define the body of the package

 

create or replace PACKAGE BODY package_name

AS

FUNCTION func_name return pipelinedDataRecordCol 

PIPELINED IS

out_record pipelinedDataRecord ;

BEGIN

FOR in_record IN(

********select statements goes here********

) LOOP

out_record := in_record;

PIPE_ROW (out_record);

END LOOP;

END func_name;

 

END package_name;

4. How to call the above pipelined function? 

SELECT * FROM TABLE(PACKAGE_NAME.FUNC_NAME);

  1. How to call stored procedure from solr | Phaneendra's Weblog
  2. How to convert reference cursor to oracle pipelined table data results | Phaneendra's Weblog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: