Re: References in Nested Tables

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 6 Jan 2000 10:33:26 -0000
Message-ID: <947154995.24111.0.nnrp-04.9e984b29@news.demon.co.uk>

Your best bet is:

    create table of person with primary key     create table of addresses with primary key     create intersection table of (person, address) indexed both ways     Put pre-row delete trigger on addresses to delete intersection.

If you REALLY want to do it with objects you can, but it is much more complicated and uses more space and runs much less efficiently.

Problems:

    Can't create an index on a REF, so no rapid access

    Can't get object's OID in a delete trigger

    PL/SQL doesn't recognise the nested table syntax.

        select    ....
                owner_table                        ot,
                table(ot.nest_table_col)       nt

    so you have to use dynamic SQL in pl/sql

As a starting point, the type of thing you need to do in 8.1 is

for i in (

        select p.id
        from person p, table(p.address_list) a
        where a.address_ref = {the ref you want to delete}

) loop

    delete from table(

            select address_list
            from person p
            where p.id = i.id
            )  t
    where
        t.address_ref = {the ref you want to delete}

    ;
end loop;

The biggest problem is that the absence on an index on the REF column guarantees a full tablescan of the nested table for every address you want to delete.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

andreak2103_at_my-deja.com wrote in message <84vkm5$tjc$1_at_nnrp1.deja.com>...
>yes, doesn't work
>In article <38710B43.2BA6D8EC_at_bcg.com>,
> Wuchun Shen <shen.wuchun_at_bcg.com> wrote:
>> did you try cascade delete?
>>
>> andreak2103_at_my-deja.com wrote:
>>
>> > Hi!
>> >
>> > I have problems with deleting References from Nested Tables - I
>guess,
>> > thats not that hard, so I hope someone can help me...
>> >
>> > I have 2 tables - e.g. Table1: Names, Table2: Adresses
>> >
>> > The first table contains in each row a nested Table in which I store
>> > the References to 1..n Adresses. If I delete an Adress I have to
>delete
>> > the Reference to it - how can I do that? I tried almost everythig,
>but
>> > nothig worked - please help me !
>> >
>> > thanks,
>> > Andrea
>> >
>> > Sent via Deja.com http://www.deja.com/
>> > Before you buy.
>>
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Thu Jan 06 2000 - 04:33:26 CST