Path: newssvr20.news.prodigy.com!newsmst01a.news.prodigy.com!prodigy.com!wn14feed!worldnet.att.net!199.218.7.141!news.glorb.com!transit.nntp.hccnet.nl!newsfeed.wirehub.nl!newsfeed.arcor.de!newsfeed.arcor-online.net!feed1.news.be.easynet.net!feed0.news.be.easynet.net!not-for-mail
From: Frederic Houbie <fh@ionicsoft.comNOSPAAAAM>
Subject: Re: delete from a view
Date: Fri, 30 Apr 2004 08:54:36 +0200
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2004.04.30.06.54.34.207978@ionicsoft.comNOSPAAAAM>
Newsgroups: comp.databases.oracle.server
References: <pan.2004.04.28.14.18.37.460759@ionicsoft.comNOSPAAAAM> <jsjv80dugbmfvdq0doe5eocldb182pmdfd@4ax.com> <pan.2004.04.28.15.54.25.890388@ionicsoft.comNOSPAAAAM> <40901CE3.AAADD344@remove_spam.peasland.com> <pan.2004.04.29.09.08.41.411595@ionicsoft.comNOSPAAAAM> <4090c859$0$20662$afc38c87@news.optusnet.com.au> <pan.2004.04.29.10.07.57.682488@ionicsoft.comNOSPAAAAM> <4090FB5F.790DD670@remove_spam.peasland.com> <pan.2004.04.29.13.37.57.550938@ionicsoft.comNOSPAAAAM> <c6r3qe$523$1@hanover.torolab.ibm.com> <pan.2004.04.29.15.38.57.214777@ionicsoft.comNOSPAAAAM> <c6rbn6$5u8$1@hanover.torolab.ibm.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
Lines: 40
Organization: [ posted via Easynet Belgium ]
NNTP-Posting-Date: 30 Apr 2004 06:54:37 GMT
NNTP-Posting-Host: 81.188.4.2
X-Trace: 1083308077 feed0.news.be.easynet.net 8985 [::ffff:81.188.4.2]:32949
X-Complaints-To: abuse@be.easynet.net
Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:260460

On Thu, 29 Apr 2004 12:49:26 -0400, Serge Rielau wrote:

> Frederic,
> 
> So each table in the UNION ALL is a another subtype in the same 
> hierarchy and the view is just merging them together again?
> I'm quite certain that Oracle supports subtables.
> You shouldn't need that view.
> Simply create a table hierarchy and then select "inclusive" from the 
> root table.
> 
> Here's how it looks in standard SQL (you may need to adjust to Oracle 
> dialect):
> CREATE TABLE root OF root_t .....
> CREATE TABLE sub1 OF sub1 UNDER root_t ....
> CREATE TABLE sub2 OF sub2 UNDER root_2 ...
> 
> SELECT * FROM root
> => returns all rows in root, sub1 and sub2
> 
> SELECT * FROM ONLY(root)
> => returns only rows in root
> 
> SELECT * FROM sub1;
> => returns all rows from sub1 and its subtables
> 
> Cheers
> Serge

The idea sounds really great but when I do this in Oracle 10g, i get

CREATE TABLE eo OF extrinsicobjectv2 UNDER RegistryObjectV2;
ERROR at line 1:
ORA-03001: unimplemented feature

.... :-(