Re: Pipelined table functions can be used inside a package?

lio.spam_at_libero.it wrote:
> Richard Kuhler <noone_at_nowhere.com> wrote in message news:<6sp4c.24487$Zo6.1016_at_twister.socal.rr.com>...
>

>>There are many bugs related to pipeline table functions still.  I've 
>>filed at least 5 myself and I found another one last week.  The one you 
>>are hitting is likely Bug No. 2982476.

We are using pipelines for some production applications so I wouldn't say the bugs are insurmountable but they can definitely lead to incorrect results and crashes. You'll have to search on MetaLink to review the bugs. Here are a couple things to avoid:

1. Use schema types instead of package type specifications (as you've discovered) (BUG 2982476).
2. Don't allow nulls with any of your input parameters (BUG 2821879).
3. Don't use the 'BETWEEN' operator with input parameters (bug not filed yet but you'll get the wrong results).
4. Don't try to open reference cursors against pipeline functions within PL/SQL and pass them to a host language (e.g. JDBC) (BUG 2968664).
5. Be carefull how much data you pass as parameters because there's a memory leak we've hit in some situations (BUG 2821879).
6. Don't expect sizing to look right at the metalevel (BUG 3037300).

> I moved all pipelined table functions outside the package and
> everything works but I don't like this solution.
> Having all the code in one package is a smartest solution and is very
> strange that Oracle don't support this.

I agree. Unfortunately, we couldn't find any workaround for the bugs so we had to go with schema types.

> I'm committed to build a solution that extracts data from the
> database, transform this data and write the results in a text file.

One of the things we use them for exactly.

> I think that a Java application that connects to the database, make a
> query like:
> SELECT field1, field2, ... fieldN FROM TABLE(my_function(parameters));
> and formats the resultset in a text file can be a good solution.

We decided to design the Java application to be completely generic so we have a PL/SQL pipeline stage that puts the record together and just sends raw data out to the Java layer for extraction to the flat file (this proved to be much faster than spooling from SQL*Plus).

> The advantage of this solution is that the formatting job (java app)
> and the extraction job (pipelined table function) can be made in
> parallel.

True. However, we found that having the Java merely do the I/O portion was significant enough since the writing was far more expensive than the formatting.

<snip>

--
Richard