Well,

At work yesterday I was showing people how to use a pipeline function.  It's funny how a such a powerful feature of Oracle 10g+ (could be 9i as well) is so under used and misunderstood.  I love using pipeline functions to do complex query logic.  The example at work was we were using a pipe lined function for a query that was a tree query.  The tree query was returning space usage by folder.  The roll up we wanted was by site instead of folder.  So the pipeline function worked great to put the data back into a query so a reporting tool could access it.  Here's an example of what we did:

CREATE OR REPLACE FUNCTION size_rpt (p_parent_value number)
RETURN size_report PIPELINED
IS
CURSOR c1
IS
SELECT stuff
FROM complex_tables
START WITH parent_id = 2
CONNECT BY parent_id = PRIOR child_id;

pc          complex_tables.parnet_id%TYPE   := p_parent_value;
site_name   complex_tables.site_name%TYPE   := NULL;
doccount    NUMBER(14)                                     := 0;
docsize     NUMBER(14)                                     := 0;
out_rec     size_rec                          := size_rec(NULL
,NULL
,NULL
);
BEGIN
FOR rec IN c1
LOOP
IF rec.parent_id = pc
THEN
IF site_name IS NOT NULL
THEN
out_rec.site_name := site_name;
out_rec.document_count := doccount;
out_rec.document_size := docsize;
PIPE ROW(out_rec);
END IF;

site_name := rec.dcollectionname;
doccount := 0;
docsize := 0;
ELSIF rec.flevel >= 3 -- We only care about level 3 and below...  level is sedo column from oracle we rename in query to flevel
THEN
doccount := doccount + NVL(rec.contentcounts
,0
);
docsize := docsize + NVL(rec.contentsizes
,0
);
END IF;
END LOOP;

out_rec.site_name := site_name;
out_rec.document_count := doccount;
out_rec.document_size := docsize;
PIPE ROW(out_rec);
END;

CREATE OR REPLACE TYPE size_rec AS OBJECT(
site_name        VARCHAR2(1020)
,document_count   NUMBER(14)
,document_size    NUMBER(14)
);

CREATE TYPE size_report AS TABLE OF size_rec;

to run it:

SELECT * FROM size_rpt(10292);

Hope this helps someone if they need to do weird stuff...  Or stuff I just love.

Thanks,

Jon