Re: Updateable view generating ORA-01779

From: Mike Taylor <miket_at_premier.co.uk>
Date: 1997/11/24
Message-ID: <65bmvf$j75$1@heliodor.xara.net>#1/1

Keith Boulton wrote in message <3475e62b.4163707_at_read.news.global.net.uk>... On Fri, 21 Nov 1997 17:33:48 -0000, "Mike Taylor" <miket_at_premier.co.uk> wrote:

>I'm trying to normalise our database. We inherited a flat file structure
 and
>as part of the migration plan I have created a view on top of the new
>normalised structure. The problem I've got is that the view will not allow
>updates. I'm using Oracle 7.3.3.4.0 on Solaris 2.5.1.

.
.
.

>create table testtab (
>keycol1 varchar2(4) not null,
>keycol2 varchar2(4) not null,
>datacol varchar2(10));
>
>insert into testtab values ('1','0','Record 1.0');
>insert into testtab values ('1','1','Record 1.1');
>insert into testtab values ('2','0','Record 2.0');
>commit;
>
>create or replace view testview (
>akeycol1, akeycol2, adatacol, bkeycol1, bkeycol2, bdatacol)
>as
>select a.keycol1, a.keycol2, a.datacol, b.keycol1, b.keycol2, b.datacol
>from testtab a, testtab b
>where a.keycol1 = b.keycol2
> and a.keycol2 = 0 or b.keycol2 = 1
> and a.keycol1 != b.keycol1
> and a.keycol2 != b.keycol2;
>
>select column_name, updatable from user_updatable_columns where table_name
 =
>'TESTVIEW';
>
>select * from testview where akeycol1 = 1;
>

---

I'm not sure I could work out which table is supposed to be updateable based on this query. You could use an instead of trigger to update the underlying tables for a view.

---

I'm trying to update the view (testview). The reason is that we have plan the migration from the old (non-normalised) to the new (normalised) structure. So the above view creates a single-row based on keycol1, which is basically the non-normalised table.
The normalised design adds keycol2 for the repeating group.

The problem is we have many modules to rewrite to correct the errors made in the original design, hence my wish to create a backwardly-compatible view. The view works, except the view cannot be updated. The reason for including a reference to user_updatable_columns is this is the view which shows if a column is updatable (see the Oracle Documentation).

I guess the problem here is with "Key-Preserved Tables" - I cannot claim to understand the manual on this!

Mike. Received on Mon Nov 24 1997 - 00:00:00 CST