Re: Nested Table Query

From: ddf <oratune_at_msn.com>
Date: Tue, 11 Sep 2012 13:42:26 -0700 (PDT)
Message-ID: <dfa4874f-ccd8-4c8b-b79d-74351814aac9_at_googlegroups.com>

On Tuesday, September 11, 2012 9:54:27 AM UTC-6, The Magnet wrote:
> On Sep 11, 10:32 am, ddf <orat..._at_msn.com> wrote:
>
> > On Tuesday, September 11, 2012 8:09:00 AM UTC-6, The Magnet wrote:
>
> > > Hi,
>
> >
>
> > > I'm trying to do a INSERT INTO (SELECT........
>
> >
>
> > > The table I am inserting into and the table I am selecting from have
>
> >
>
> > > the exact same layout.  However, both tables have nested tables
>
> >
>
> > > within.  I'm having such a hard time finding the right SQL syntax.
>
> >
>
> > > All the examples I am finding use literals to insert into the nested
>
> >
>
> > > table.  Here, I am trying to select from one table and insert into
>
> >
>
> > > another.
>
> >
>
> > > Table 1
>
> >
>
> > > ----------
>
> >
>
> > > Column 1
>
> >
>
> > > Column 2
>
> >
>
> > > Nested Table 1
>
> >
>
> > >    Nested Column 1
>
> >
>
> > >    Nested Column 2
>
> >
>
> > > Table 2
>
> >
>
> > > ----------
>
> >
>
> > > Column 1
>
> >
>
> > > Column 2
>
> >
>
> > > Nested Table 1
>
> >
>
> > >    Nested Column 1
>
> >
>
> > >    Nested Column 2
>
> >
>
> > > INSERT INTO table2
>
> >
>
> > >   SELECT t1.column1, t2.column2, n1.nested_column1, n1.nested_column2
>
> >
>
> > >   FROM table1 t1, TABLE(nested_table1) n1;
>
> >
>
> > > That did not work.   Can anyone help with the syntax?
>
> >
>
> > > Thanks!
>
> >
>
> > You're apparently trying too hard to get this done; if the two tables have the exact same layout (same column definitions and same nested table definitions) then it's easier than you  thought:
>
> >
>
> > SQL> CREATE TYPE people_typ AS OBJECT (
>
> >   2             last_name      VARCHAR2(25),
>
> >   3             department_id  NUMBER(4),
>
> >   4             salary         NUMBER(8,2));
>
> >   5  /
>
> >
>
> > Type created.
>
> >
>
> > SQL>
>
> > SQL> CREATE TYPE people_tab_typ AS TABLE OF people_typ;
>
> >   2  /
>
> >
>
> > Type created.
>
> >
>
> > SQL>
>
> > SQL> CREATE TABLE hr_info (
>
> >   2             department_id   NUMBER(4),
>
> >   3             location_id     NUMBER(4),
>
> >   4             manager_id      NUMBER(6),
>
> >   5             people          people_tab_typ)
>
> >   6     NESTED TABLE people STORE AS people_stor_tab;
>
> >
>
> > Table created.
>
> >
>
> > SQL>
>
> > SQL> CREATE TABLE hr_info_cpy (
>
> >   2             department_id   NUMBER(4),
>
> >   3             location_id     NUMBER(4),
>
> >   4             manager_id      NUMBER(6),
>
> >   5             people          people_tab_typ)
>
> >   6     NESTED TABLE people STORE AS people_stor_tab_cpy;
>
> >
>
> > Table created.
>
> >
>
> > SQL>
>
> > SQL> INSERT INTO hr_info VALUES (280, 1800, 999, people_tab_typ());
>
> >
>
> > 1 row created.
>
> >
>
> > SQL>
>
> > SQL> INSERT INTO TABLE(SELECT h.people FROM hr_info h
>
> >   2             WHERE h.department_id = 280)
>
> >   3     VALUES ('Smith', 280, 1750);
>
> >
>
> > 1 row created.
>
> >
>
> > SQL>
>
> > SQL> commit;
>
> >
>
> > Commit complete.
>
> >
>
> > SQL>
>
> > SQL> select * from hr_info;
>
> >
>
> > DEPARTMENT_ID LOCATION_ID MANAGER_ID
>
> > ------------- ----------- ----------
>
> > PEOPLE(LAST_NAME, DEPARTMENT_ID, SALARY)
>
> > --------------------------------------------------------------------------------
>
> >           280        1800        999
>
> > PEOPLE_TAB_TYP(PEOPLE_TYP('Smith', 280, 1750))
>
> >
>
> > SQL>
>
> > SQL> insert into hr_info_cpy
>
> >   2  select * from hr_info;
>
> >
>
> > 1 row created.
>
> >
>
> > SQL>
>
> > SQL> select * From hr_info_cpy;
>
> >
>
> > DEPARTMENT_ID LOCATION_ID MANAGER_ID
>
> > ------------- ----------- ----------
>
> > PEOPLE(LAST_NAME, DEPARTMENT_ID, SALARY)
>
> > --------------------------------------------------------------------------------
>
> >           280        1800        999
>
> > PEOPLE_TAB_TYP(PEOPLE_TYP('Smith', 280, 1750))
>
> >
>
> > SQL>
>
> >
>
> > David Fitzjarrell
>
>
>
>
>
> David,
>
>
>
> Your solution worked! Thanks. I'm just not clear on one thing:
>
> The tables had the same exact layout. The only difference being that
>
> I created separate types for the source and destination table.
>
>
>
> In your code you use the same types, but a different STORE AS clause.
>
> I'm not sure I understand how Oracle is seeing them as different.
>
> Aside from the fact they are different objects, the layouts are the
>
> same.......

You can do either; I went the easy route and reused the type under a different name so Oracle saw them as being different enough. Without changing the STORE AS naming the second table failed to create (there cannot be two objects in the same schema with the same name). As long as the object names were different Oracle saw them as two different tables, including the nested tables.

David Fitzjarrell Received on Tue Sep 11 2012 - 15:42:26 CDT