? How do you insert data into Nested Ref Tables

From: netguru <netguru_at_swlink.net>
Date: Sat, 24 Nov 2001 14:34:50 -0700
Message-ID: <9tp40o025ep@enews2.newsguy.com>

Hello,

I am trying to use nested relations. The Oracle 8i/9i documentation does not cover this very much. I have tried various combinations of syntax, but each time SQLPlus rejects my 'INSERT' commands.

Can some one help please.

Using Object-relational Tables

---

CREATE or REPLACE TYPE sw_TY AS OBJECT
(

   swTitle        VARCHAR2(30),
   swCompany      VARCHAR2(40),
   swVersion      VARCHAR2(10)

);

CREATE or REPLACE TYPE sw_NT_Ref AS TABLE OF REF sw_TY;

CREATE TABLE allSoftware
(

   hwName      VARCHAR2(10),
   swTableRef     sw_NT_Ref

)
NESTED TABLE swTableRef STORE AS SoftwareRefTable;

From Oracle documentation it appears that I can select from the table :

SELECT sw.COLUMN_TYPE.swVersion
  FROM THE (SELECT swTableRef

            FROM allsoftware
            WHERE hwName = "xyz"
           )sw

WHERE sw.COLUMN_VALUE.swTitle = "MySoftware";

...... but I need to get data into the relation/table first

Question:

---

How do you insert into the nested table 'swTableRef' ?

Or is there someway that this is mapped to an existing   table and Oracle generates the Reference Ids ?

Thanks for your help, Received on Sat Nov 24 2001 - 15:34:50 CST