Re: ? How to insert table in nested Tables

From: Job Miller <jobmiller_at_yahoo.com>
Date: 25 Nov 2001 16:47:19 -0800
Message-ID: <2edf8ed9.0111251647.c6f2421_at_posting.google.com>


if you want to add to an existing nested table the documenation says you do something like this:

from:

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/appdev.817/a77069/04_colls.htm#20230

BEGIN
   INSERT INTO

      TABLE(SELECT swTableRef FROM allsoftware WHERE hwName = 'xyz' )
      VALUES('a', 'b', 'b');

END; identically to the select.

the initial insert can be done with what the previous repsonder suggested.
I am not sure if that would work if there was already stuff present in the nested table though

Job

"netguru" <netguru_at_swlink.net> wrote in message news:<9tp3l8024q7_at_enews2.newsguy.com>...
> Hello,
>
> I am trying to use nested relations. The Oracle 8i/9i documentation
> does not cover this very much. 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 Mon Nov 26 2001 - 01:47:19 CET

Original text of this message