Re: PL/SQL - Returning resultset-like values from functions

On Sun, 11 Aug 2002 16:15:24 -0000, "Carsten Alexander" <acamat_at_web.de> wrote:

>Hi, all
>
>I'm quite new in PL/SQL but experienced in other program languages. I started
>with some basic functions returning a single value from function so I can use
>the syntax
>
>SELECT [Package].[Function(Parameter)] FROM DUAL
>
>This works already fine.
>
>What comes next is the implementation of function which return resultset-like
>structures of non-existing tables. Can I accomplish syntaxes like
>
>SELECT [Package].[Function(Parameter)].[Member] FROM DUAL or
>SELECT [Package].[Function(Paramter)] FROM DUAL with the output
>
>%: Member1 Member2 Member3
>%: -----------------------
>%: Value1 Value2 Value3
>
>The members are basic types. Any hint or information would be very helpful.

Nesting functions SELECTing from tables is a BAD idea. You will always get recursive sql as a result of that, the optimizer won't merge the two levels of sql into one.
If you plan to go on this road you will create very inefficient applications.

Also you can't return a resultset from a function you need a procedure with an IN OUT parameter of type REF CURSOR

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Aug 11 2002 - 10:37:50 CDT