Updateable view generating ORA-01779

From: Mike Taylor <miket_at_premier.co.uk>
Date: 1997/11/21
Message-ID: <654glt$p03$1@heliodor.xara.net>#1/1

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.

I think I'm onto a loser here as I've RTFM and The App Dev Guide definately states that a join view can only ever update one underlying table (p4-18). My problem is it is failing due to the fact that my view appears to be on a non-key preserved table (same page in App Dev Guide). As you can see from the example below I'm returning two rows from the same table.

No matter what I try, I cannot get the columns of my view to go updatable according to USER_UPDATABLE_COLUMNS.

Any help would be welcome - I've set up a small test case here (just run in on SQL*Plus):

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;

My actual view is slightly different from the above test case as it has to deal with the fact that the "b" record may not exist, so the where portion is actually:

 where b.keycol1 (+) = a.keycol1
   and (a.keycol2 = 1 or b.keycol2 = 2)
   and a.keycol2 != b.keycol2 (+)

So, the view works fine, I can select as if the old table was still in place, but if I try to update it, I get ORA-01779.

Thanks,

Mike Taylor. Received on Fri Nov 21 1997 - 00:00:00 CST