Urgent: PL/SQL with table()-Operator

Hi,

I have following problem:

Using the SQL Worksheet (Oracle8i, V8.1.5) a SQL-Query like

select ref(p) from persons p, table(p.childs) c

    where p.sex='male' and c.name='PETER';

spends some nice and correct refs on the screen.

Packing this into a PL/SQL-Stored Function inside a package and trying to CREATE it fails with the message that I have to declare "p.childs" (or something alike, this is out of my mind).

To be exact the PL/SQL Code looks like:

...

create or replace function malePersonsWithChildWithName(inname in varchar2) return person_ref_nt_type is

    person_ref_nt person_ref_nt_type;
begin

    select ref(p) bulk collect into person_ref_nt     from persons p, table(p.childs) c
    where p.sex='male' and c.name=inname;

    return person_ref_nt;
end malePersonsWithChildWithName;
...

Any idea how to get this going? Since this join with the table()-operator is MUCH faster than building a collection out of the desired REF(t)-s using for-loops or something similar I die for an idea how to get this right. The original Query is a more complicated but similar.

Thanks in advance for quick answers to ulf.heyder_at_gmx.de Ulf Received on Fri Oct 06 2000 - 16:07:50 CDT