Re: Nested Table Query
Date: Wed, 12 Sep 2012 08:51:52 -0700 (PDT)
Message-ID: <3916ef0b-069b-47d8-b9ff-e42206381867_at_kr6g2000pbb.googlegroups.com>
On Sep 11, 8:39 pm, The Magnet <a..._at_unsu.com> wrote:
> On Sep 11, 8:29 pm, ddf <orat..._at_msn.com> wrote:
>
>
>
>
>
>
>
>
>
> > On Tuesday, September 11, 2012 9:47:07 AM UTC-6, joel garry wrote:
> > > On Sep 11, 8:32 am, ddf <orat..._at_msn.com> wrote:
>
> > > > SQL>
>
> > > > SQL> insert into hr_info_cpy
>
> > > > 2 select * from hr_info;
>
> > > > 1 row created.
>
> > > Somehow that strikes me as "considered bad practice." The order of
>
> > > columns, like anything else without explicit ordering, is not
>
> > > guaranteed. Try adding columns a and b to the first table, b and a to
>
> > > the second, and see if those are selected into the correct place. Any
>
> > > modification of either table could potentially do this.
>
> > > jg
>
> > > --
>
> > > _at_home.com is bogus.
>
> > >http://www.crn.com.au/News/315110,oracle-sues-partner-over-theft-clai...
>
> > Understood. The original problem stated the source and destination tables were exactly the same in definition, down to the nested tables. Thus I took the easy way out. I do agree that is not the ideal way to solve this problem; I should get cracking and find a more holistic and general solution not based on such assumptions.
>
> > I'll take my 50 lashes with the wet noodle gracefully.
Your help is generally so good, I always have it in the back of my mind "what obvious thing am I missing?" if I have to, er, lash out.
>
> > David Fitzjarrell
>
> Well, when I defined the tables with their own object types (though
> the object types were identical in structure) I'd either get an error
> of 'not enough values' or something related to object ID.
>
> Real strange. Seems like the reusing of the object types, though
> different STORE AS clauses, somehow told Oracle that it was ok to
> perform this simple SELECT AS INSERT.........
>
> Oracle = "We can do everything easily, but we make it difficult for
> you."
Interesting. Hard to say if it is a bug or laziness on Oracle's part without replicating the problem, but it's all written by humans at some point. I'm not up with type definition, but maybe you ran into something analogous to what I said about column order. Wild speculation, I wonder if it is doing something like the edition view Tom talks about: http://www.oracle.com/technetwork/issue-archive/2010/10-mar/o20asktom-098897.html (Or perhaps, that's what it should be doing and isn't. I have no idea what I'm talking about.)
jg
-- _at_home.com is bogus. http://www.computerworlduk.com/news/infrastructure/3380827/jury-hear-second-phase-of-hp-oracle-itanium-case-in-february-2013/Received on Wed Sep 12 2012 - 10:51:52 CDT