Re: Use function in SELECT with array/record as return value?

Timo Neumann <t.neumann_at_ff-muenchen.de> wrote in message news:<vGhka.3112$506.3841_at_typhoon.bart.nl>...
> Hi all,
>
> I would like to use a function to return more than one value per call. I
> understand that I can use varrays, PL/SQL tables, or records.
> My problem is, that I need to call the function from a SQL SELECT statement.
>
> Here is an example: I've got this tables:
>
> tbPerson ( personid, lastname, firstname );
> tbAddress( addressid, personid, street, zip, town, addresstype );
>
> As you can see one person can have multiple addresses.
>
> I want the following view:
>
> vwPersonAddress( personid, name, street, zip, town )
>
> I would like to fetch street, zip, and town with a function.
> Sth. like:
>
> SELECT p.personid, p.name,
> PKG.getAddress( a.addressid, 'Home' )
> FROM tbPerson p, tbAddress a
>
> Is this possible? Or can I use only "primitive" datatypes as return
> values if calling from DQL?
>
>
> Thanks for your help,
>
> Timo

Hi,

as long as your function returns a SQL collection type (nested table / varray), then you can use the TABLE(<function>) operator and select from it directly, i.e. SELECT * FROM TABLE(<function>) Received on Tue Apr 08 2003 - 05:45:03 CDT