MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Pipelined function update

with 3 comments

When I presented the concept at the Utah Oracle User’s Group (UTOUG) Training Days 3/12-3/13/2009 it became clear the community could benefit from more detail about table and pipelined table functions. The question asked was: “What’s the primary purpose for pipelined table functions?”

My answer is: The primary purpose of a pipelined table function lets you retrieve PL/SQL record collection structures in a SQL context.

If there’s another reason that I missed, please let me know. It took a couple days to expand the older post to be more complete.

You can see the new one here …

Written by maclochlainn

March 17th, 2009 at 10:22 pm

Posted in Oracle,pl/sql,sql

3 Responses to 'Pipelined function update'

Subscribe to comments with RSS or TrackBack to 'Pipelined function update'.

  1. Worth pointing out that a table function doesn’t have to be pipelined, and even a non-pipelined version can be used from SQL.

    The benefits of the pipeline is that the query sees rows before the entire result set is generated by the function (which can be faster) and that memory requirements may be smaller because you don’t need to hold the full result set in the PL/SQL/UGA memory.

    The downside of the pipeline clause is that the PIPE ROW line has to be in the top level function block so it imposes restrictions on your code structure which might make it ugly.

    Gary

    21 Mar 09 at 2:58 pm

  2. Are you referring to table functions that returns a scalar collection, which is a SQL data type? A table function with a scalar collection is shown in this Java library wrapped by a PL/SQL function blog post. If you’re referring to using a record collection table function, it would be great if you’d post an example.

    maclochlainn

    21 Mar 09 at 7:20 pm

  3. Yes, it does need to be a SQL Type, but you can create a record type and a collection of that record type.

    CREATE TYPE rec IS object
      (val1 VARCHAR2(100)
      ,num1 NUMBER);
    /
     
    CREATE TYPE tab_rec IS TABLE OF rec
    /
     
    CREATE OR REPLACE FUNCTION obj_coll RETURN tab_rec IS
      t_temp tab_rec := tab_rec();
      r_rec  rec;
    BEGIN
      t_temp.extend(2);
      r_rec := rec('hello',10);
      t_temp(1) := r_rec;
      r_rec.val1 := 'world';
      t_temp(2) := r_rec;
      RETURN t_temp;
    END;
    /
    SELECT * FROM TABLE(obj_coll);

    In the background, a pipelined function will create those SQL types for you from the PL/SQL types. You can see them in USER_TYPES with some ugly SYS_PLSQL type names. Because of those names (which can vary between dev/test/prod), I’d prefer using explicitly named types anyway.

    Gary

    22 Mar 09 at 5:32 pm

Leave a Reply