Re: Updateable view generating ORA-01779

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.
>
>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.
>
>

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. Received on Fri Nov 21 1997 - 00:00:00 CST