After Column Encryption, Update On View Fails With ORA-01779 (Doc ID 1087701.1)

Last updated on MAY 21, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.4 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

Column encryption has been added on a primary key column. A view was built on the TDE table and other unencrypted tables. The update statements on the view fail with ORA-01779:

create table tde1( num number, value varchar2(10)); 
create table tde2(num number, value varchar2(20)); 
insert into tde1 values(1,'a'); 
insert into tde2 values(1, 'b'); 
commit; 
alter table tde1 add constraint tde1_pk primary key (num); 
alter table tde2 add constraint tde2_pk primary key (num); 
 
This update statement works fine:

update (select a.value value1, b.value value2 from tde1 a, tde2 b where a.num = b.num) tab1
set tab1.value1=tab1.value2;


The same update statement fails with error ORA-01779 after adding column encryption:

alter table tde1 modify (num encrypt no salt);
update (select a.value value1, b.value value2 from tde1 a, tde2 b where a.num = b.num) tab1
set tab1.value1=tab1.value2;

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms