pipelined functions in oracle 9i database

[Norse]

Hello Experts !

I am not familiar with some database objects from earlier.I have started my study from oracle 9i and some one asked me suddenly that have you any idea about pipeline functions in oracle !I was unable to reply for his question.I am entirely unknown from this very concept.So please help and introduce me about this concept.

Thanks .

[kelfro]

pipelined functions in oracle 9i database :

The Oracle 8.0 introduced with a concept of accessing a collection of data which is usually loaded through a function as a "virtual table".This strategy and technique became available with more features in the 8i timeframe just because of Tom Kyte and number of web forums where "SELECT FROM TABLE(CAST(plsql_function AS collection_type))" became a simple and common technique to bind the lists of data generated by the user.

In the upcoming 9i Release 1 (9.0), Oracle has introduced to user with new concept called pipelined table functions are also known as pipelined functions with some advance techniques and reliable form.

[Zecho]

How Pipeline works :

When oracle comes with this new feature called pipelined table functions,It builds on the table function principle but with some critical differentiations and perform the manipulation task in this way:

First of all , data is piped also treated as streamed to the caller in a small arrays of prepared data, rather than fully materialized as with original table functions.

And in second phase, according to the rule Oracle can parallelise the pipelined functions that means the PL/SQL can be executed by multiple slaves for the first attempt and

In the Third phase,pipelined functions make it enable to switch PL/SQL procedures into row sources for heavy SQL operations, concatenating large and complex conversion logic with the benefits of SQL.

[Reegan]

simple pipelined function :
To view the functionality of any pipelined function,we need the following elements to be ready :

• A collection type
• Thepipelined PL/SQL function inside a package.

Creating Collection type :

SQL> CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;

The above created type will structured according to the data that we wish to pipe.

[opaper]

Creating pipelined functions :

Now,you can create your first pipelined function.I will make a simple and standalone function using packaged functions.The code is given below :

SQL> CREATE FUNCTION Tuple_generator (rows_in IN PLS_INTEGER )
RETURN number_ntt PIPELINED IS
BEGIN
FOR loop_var IN 1 .. rows_in LOOP
PIPE ROW (loop_var);
END LOOP;

RETURN;
END;

There are several things that need to be concentrate :

• The return type (NUMBER_NTT) must be a collection type which is based on numbers.
• PIPE ROW syntax to add a single record into the return collection stream

[MindSpace]

Execution of pipelined functions :

To test the created function, I need fire the SELECT query with the name of pipeline function which will be as follows :

SQL> SELECT * FROM   TABLE( row_generator(5) );

It will generate 20 Number respectively according to the parameter like :

COLUMN_VALUE
------------
1
2
3
4
5


5 rows selected.