Re: possible to return modified ref cursor?

Bomb Diggy wrote:
>
> Hi,
>
> I know how to do the standard Java-to-PL/SQL-REF CURSOR thing for
> returning ResultSets from stored procedures, but now I have a very
> complicated (impossible for me) SQL statement that I wanted to break
> up into several smaller statements. So, I want to essentially build a
> list of objects and return them to the caller. In PL/SQL, I would
> guess the nomenclature would be 'build a Collection of Types and
> return to caller via REF CURSOR'. (??)
>
> The answer may have something to do with Collections or temporary
> tables. (??)

<snip>

You didn't say what version of Oracle you're using (does anybody ever?). 9i introduces a new feature called 'pipeline table functions'. These allow you to use pl/sql to construct rows of data and return them iteratively (rather than collecting them all in memory first). With this new feature you can do something like ...

select *
from table(my_pipeline_function(my_parameters));

You can of course open that query in a reference cursor as well. I will warn you that there are a few bugs still. I'd strongly suggest you check metalink and get familiar with the issues.

Richard Received on Sat Jun 21 2003 - 18:59:06 CDT