CODEWITHSUNDEEP
sqloracle-databasereferencenested-tableobject-oriented-database
Guilhem Fry
Guilhem Fry

Reputation: 326

Insert multiple references in a nested table

I have the table customer_table containing a list (nested table) of references toward rows of the account_table.

Here are my declarations :

Customer type:

CREATE TYPE customer as object(
    custid integer,
    infos ref type_person,
    accounts accounts_list
);

accounts_list type:

CREATE TYPE accounts_list AS table of ref account;

Table:

CREATE TABLE customer_table OF customer(
    custid primary key,
    constraint c_inf check(infos is not null),
    constraint c_acc check(accounts is not null)
)
NESTED TABLE accounts STORE AS accounts_refs_nt_table;

So I would like to insert multiple refs in my nested table when I create a customer, as an account can be shared.

I can't find out how to do that.

I tried:

INSERT INTO customer_table(
    SELECT 0,
    ref(p),
    accounts_list(
        SELECT ref(a) FROM account_table a WHERE a.accid = 0
        UNION ALL 
        SELECT ref(a) FROM account_table a WHERE a.accid = 1
    )
    FROM DUAL
    FROM person_table p
    WHERE p.personid = 0
);

With no success.

Thank you

Upvotes: 0

Views: 1011

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can use the collect() function, e.g. in a subquery:

INSERT INTO customer_table(
    SELECT 0,
    ref(p),
    (
      SELECT CAST(COLLECT(ref(a)) AS accounts_list) 
      FROM account_table a
      WHERE accid IN (0, 1)
    )
    FROM person_table p
    WHERE p.personid = 0
);

As the documentation says, "To get accurate results from this function you must use it within a CAST function", so I've explicitly cast it to your account_list type.

If you don't want a subquery you could instead do:

INSERT INTO customer_table(
    SELECT 0,
    ref(p),
    CAST(COLLECT(a.r) AS accounts_list)
    FROM person_table p
    CROSS JOIN (SELECT ref(a) AS r FROM account_table a WHERE accid IN (0, 1)) a
    WHERE p.personid = 0
    GROUP BY ref(p)
);

but I think that's a bit messier; check the performance of both though...

Upvotes: 1

Related Questions