Results 1 to 6 of 6

Thread: pipelined functions in oracle 9i database

  1. #1
    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 .

  2. #2
    Join Date
    Apr 2008
    Posts
    2,010

    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.

  3. #3
    Join Date
    May 2008
    Posts
    2,302

    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.

  4. #4
    Join Date
    Oct 2005
    Posts
    2,394

    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.

  5. #5
    Join Date
    May 2008
    Posts
    2,393

    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 :

    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

  6. #6
    Join Date
    Feb 2008
    Posts
    1,856

    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.

Similar Threads

  1. Statpack in oracle database
    By Reegan in forum Networking & Security
    Replies: 5
    Last Post: 16-02-2010, 07:00 PM
  2. Dynamic SQL in Oracle database
    By Adriana_andros in forum Software Development
    Replies: 5
    Last Post: 11-02-2010, 06:49 PM
  3. Subqueries in oracle database
    By Landan in forum Software Development
    Replies: 4
    Last Post: 09-02-2010, 04:57 PM
  4. Single database & multiple functions in web services.
    By Kiran123 in forum Software Development
    Replies: 2
    Last Post: 31-01-2009, 03:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •