Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



pipelined functions in oracle 9i database

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 11-02-2010
Member
 
Join Date: Jan 2010
Posts: 48
pipelined functions in oracle 9i database
  

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 .

Reply With Quote
  #2  
Old 11-02-2010
Member
 
Join Date: Apr 2008
Posts: 1,999
pipelined functions in oracle 9i database

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.
Reply With Quote
  #3  
Old 11-02-2010
Member
 
Join Date: May 2008
Posts: 2,290
pipelined functions:Principle of working

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.
Reply With Quote
  #4  
Old 11-02-2010
Member
 
Join Date: Oct 2005
Posts: 2,389
simple pipelined function in oracle 9i database

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 :

Code:
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.
Reply With Quote
  #5  
Old 11-02-2010
Member
 
Join Date: May 2008
Posts: 2,381
Creating pipelined functions in oracle 9i database

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 :

Quote:
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
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #6  
Old 11-02-2010
Member
 
Join Date: Feb 2008
Posts: 1,845
Executing pipelined functions in oracle 9i database

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 :

Code:
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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "pipelined functions in oracle 9i database"
Thread Thread Starter Forum Replies Last Post
Statpack in oracle database Reegan Networking & Security 5 16-02-2010 07:00 PM
Dynamic SQL in Oracle database Adriana_andros Software Development 5 11-02-2010 06:49 PM
Subqueries in oracle database Landan Software Development 4 09-02-2010 04:57 PM
Single database & multiple functions in web services. Kiran123 Software Development 2 31-01-2009 03:24 PM


All times are GMT +5.5. The time now is 01:43 PM.