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

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 Received on Mon Apr 07 2003 - 11:28:11 CDT