Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Prince.

Asked: August 30, 2002 - 9:18 pm UTC

Last updated: May 14, 2012 - 11:58 pm UTC

Version: 8.1.7.4

Viewed 10K+ times! This question is

You Asked

--This is updatable
CREATE OR REPLACE VIEW V_myView AS
SELECT
A.CNY#,
A.RECORD#,
A.STATUS,
A.TIMEUnit
FROM myTable A
WHERE (A.CNY#, nvl(A.LOCATION#,0)) IN
( SELECT SB.CNY#, SB.LOCATIONKEY
FROM mySlb SB, myUSERPROF U
WHERE SB.SESSIONKEY = sys_context('userenv','SESSION_USER')
AND SB.CNY# = U.CNY#
AND SB.SESSIONKEY = U.SESSION#
)
/

--This view is not updatable. All the columns from all_updatable_columns show they are not updatable!

Can I rewrite the above with join, and still be able to update/insert the view?

CREATE OR REPLACE VIEW V_myView AS
SELECT
A.CNY#,
A.RECORD#,
A.STATUS,
A.TIMEUnit
FROM myTable A, mySlb slb, myUSERPROF u1
where a.cny# = slb.cny#
and nvl(a.location#,0) = slb.locationkey
and slb.SESSIONKEY = sys_context('userenv','SESSION_USER')
and slb.CNY# = U1.CNY#
and slb.SESSIONKEY = U1.SESSION# ;

Note, I tried without the NVL function, as well.

Thanks.

and Tom said...

Well, it has to do with KEY Preserved tables, see

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:548422757486 <code>

to read about that concept.

Basically -- in the first view -- it understands that each row in myTable will (can) appear in the view AT MOST ONCE. A given row in myTable will be selected at most ONCE in the view. (eg: if you join EMP to DEPT and given that a primary key exists on DEPTNO in DEPT, you know that each row in EMP will be returned AT MOST once. EMP is "key preserved". DEPT on the other hand -- well, each row in DEPT might be returned 0, 1 or more times -- DEPT is not key preserved).

We only allow modifications to objects in the key preserved table.


Using the IN assures us (because there is only ONE table in the FROM clause of the view) that the rows are in fact key preserved.


Moving onto your other one -- if the right keys are in place AND we drop the NVL() which clouds the issue -- the view can be key preserved. Consider:



ops$tkyte@ORA920.US.ORACLE.COM> create table myTable
2 ( cny# int, record# int, status int, timeUnit int, location# int );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table mySlb ( cny# int, locationkey int, sessionkey int );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create table myuserprof ( cny# int, session# int );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> CREATE OR REPLACE VIEW V_myView AS
2 SELECT
3 A.CNY#,
4 A.RECORD#,
5 A.STATUS,
6 A.TIMEUnit
7 FROM myTable A, mySlb slb, myUSERPROF u1
8 where a.cny# = slb.cny#
9 and nvl(a.location#,0) = slb.locationkey
10 and slb.SESSIONKEY = sys_context('userenv','SESSION_USER')
11 and slb.CNY# = U1.CNY#
12 and slb.SESSIONKEY = U1.SESSION# ;

View created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select column_name,UPDATABLE,
2 insertable,deletable
3 from user_updatable_columns
4 where table_name = 'V_MYVIEW'
5 /

COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
CNY# NO NO NO
RECORD# NO NO NO
STATUS NO NO NO
TIMEUNIT NO NO NO


that is what you are seeing now...


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter table myslb
2 add constraint myslb_pk primary key(cny#,locationkey);

Table altered.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter table myuserprof
2 add constraint myuserprof_pk primary key(cny#,session#);

Table altered.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> CREATE OR REPLACE VIEW V_myView AS
2 SELECT
3 A.CNY#,
4 A.RECORD#,
5 A.STATUS,
6 A.TIMEUnit
7 FROM myTable A, mySlb slb, myUSERPROF u1
8 where a.cny# = slb.cny#
9 and nvl(a.location#,0) = slb.locationkey
10 and slb.SESSIONKEY = sys_context('userenv','SESSION_USER')
11 and slb.CNY# = U1.CNY#
12 and slb.SESSIONKEY = U1.SESSION# ;

View created.

ops$tkyte@ORA920.US.ORACLE.COM> /

View created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select column_name,UPDATABLE,
2 insertable,deletable
3 from user_updatable_columns
4 where table_name = 'V_MYVIEW'
5 /

COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
CNY# NO NO NO
RECORD# NO NO NO
STATUS NO NO NO
TIMEUNIT NO NO NO


same thing as before, it is because of the NVL on the table we are trying to keypreserve


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> CREATE OR REPLACE VIEW V_myView AS
2 SELECT
3 A.CNY#,
4 A.RECORD#,
5 A.STATUS,
6 A.TIMEUnit
7 FROM myTable A, mySlb slb, myUSERPROF u1
8 where a.cny# = slb.cny#
9 and a.location# = slb.locationkey
10 -- and nvl(a.location#,0) = slb.locationkey
11 and slb.SESSIONKEY = sys_context('userenv','SESSION_USER')
12 and slb.CNY# = U1.CNY#
13 and slb.SESSIONKEY = U1.SESSION# ;

View created.

ops$tkyte@ORA920.US.ORACLE.COM> /

View created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select column_name,UPDATABLE,
2 insertable,deletable
3 from user_updatable_columns
4 where table_name = 'V_MYVIEW'
5 /

COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
CNY# YES YES YES
RECORD# YES YES YES
STATUS YES YES YES
TIMEUNIT YES YES YES

ops$tkyte@ORA920.US.ORACLE.COM>

and there you go -- no it is the same as your IN view


Is there another workaround? Yes, namely INSTEAD of triggers. You can code the logic behind the INSERT yourself. Consider:


ops$tkyte@ORA920.US.ORACLE.COM> CREATE OR REPLACE VIEW V_myView AS
2 SELECT
3 A.CNY#,
4 A.RECORD#,
5 A.STATUS,
6 A.TIMEUnit
7 FROM myTable A, mySlb slb, myUSERPROF u1
8 where a.cny# = slb.cny#
9 and nvl(a.location#,0) = slb.locationkey
10 and slb.SESSIONKEY = sys_context('userenv','SESSION_USER')
11 and slb.CNY# = U1.CNY#
12 and slb.SESSIONKEY = U1.SESSION# ;

View created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select column_name,UPDATABLE,
2 insertable,deletable
3 from user_updatable_columns
4 where table_name = 'V_MYVIEW'
5 /

COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
CNY# NO YES NO
RECORD# NO YES NO
STATUS NO YES NO
TIMEUNIT NO YES NO

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into v_myView values ( 1,1,1,1 );
insert into v_myView values ( 1,1,1,1 )
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


ops$tkyte@ORA920.US.ORACLE.COM> create or replace trigger v_myView_IOI
2 instead of insert on v_myView
3 begin
4 insert into myTable ( cny#, record#, status, timeUnit )
5 values ( :new.cny#, :new.record#, :new.status, :new.timeUnit );
6 end;
7 /

Trigger created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into v_myView values ( 1,1,1,1 );

1 row created.


Rating

  (30 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent explantion with examples. Not insertable view with UQ key and NVL in qry.

Prince,, August 31, 2002 - 4:21 pm UTC 

Thanks Tom, for the detailed explanation.

I am aware of the instead of trigger and  thought of getting it done straight.

The question, I had in mind was though both queries are the same, why oracle couln't find the joined view is key preserved. But, by definition the joining tables (other than the key preserved one) should have unique/pk 

BTW, if I convert the join/in with exists, it works.

*****
Aslo, at home, I am testing this on 9.2.0.1. But some reason, I don't get the same result you got.

SQL> create table myTable ( cny# int, record# int, status int, timeUnit int, location# int );

Table created.

SQL> create table mySlb ( cny# int, locationkey int, sessionkey int );

Table created.

SQL> create table myuserprof ( cny# int, session# int );

Table created.

SQL>
SQL> alter table myslb add constraint myslb_pk primary key(cny#,locationkey);

Table altered.

SQL> alter table myuserprof add constraint myuserprof_pk primary key(cny#,session#);

Table altered.

SQL>
SQL>
SQL> CREATE OR REPLACE VIEW V_myView AS
  2  SELECT A.CNY#,A.RECORD#, A.STATUS, A.TIMEUnit
  3  FROM myTable A, mySlb slb, myUSERPROF u1
  4  where a.cny# = slb.cny#
  5  and nvl(a.location#,0) = slb.locationkey
  6  and slb.SESSIONKEY = sys_context('userenv','SESSION_USER')
  7  and slb.CNY# = U1.CNY#
  8  and slb.SESSIONKEY = U1.SESSION# ;

View created.

SQL> select column_name,UPDATABLE,
  2            insertable,deletable
  3      from user_updatable_columns
  4      where table_name = 'V_MYVIEW';

COLUMN_NAME                    UPD INS DEL
------------------------------ --- --- ---
CNY#                           NO  NO  NO
RECORD#                        NO  NO  NO
STATUS                         NO  NO  NO
TIMEUNIT                       NO  NO  NO

SQL>

Note, You have the view as INSERTABLE, though not UPDATABLE/DELETABLE. Mine shows, no DMLs allowed. I am wondering why is it so? But the final result seem to  be the same in both cases. You get ORA-01732 when trying to insert.

Have I missed anything?

*****

Tom Kyte
August 31, 2002 - 4:37 pm UTC

Mine didn't show insertable - it is the NVL() on the tables column that is breaking the "key preserved".

Here is a simplier rule for you:

if the FROM clause in the view contains a single table, it is modifiable. That is why the IN/Where exists works, there is but ONE TABLE in the view (forget subqueries, they don't count). By definition it must be key preserved.

When there is more then one table, you must be joining to primary/unique constraints on the n-1 tables and you cannot apply any functions to the tables columns you are trying to key preserve.

So, if you change line 5 above to not have the NVL(), it'll be updatable.

My question is.

Prince., August 31, 2002 - 4:55 pm UTC 

Thanks Tom,

I understand that point. But my question is

-------------------------------------------------------
ops$tkyte@ORA920.US.ORACLE.COM> CREATE OR REPLACE VIEW V_myView AS
  2  SELECT
  3                  A.CNY#,
  4                  A.RECORD#,
  5                  A.STATUS,
  6                  A.TIMEUnit
  7  FROM       myTable A, mySlb slb, myUSERPROF u1
  8  where      a.cny# = slb.cny#
  9  and        nvl(a.location#,0) = slb.locationkey
 10  and        slb.SESSIONKEY = sys_context('userenv','SESSION_USER')
 11  and        slb.CNY#            = U1.CNY#
 12  and        slb.SESSIONKEY = U1.SESSION# ;

View created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select column_name,UPDATABLE,
  2          insertable,deletable
  3    from user_updatable_columns
  4   where table_name = 'V_MYVIEW'
  5  /

COLUMN_NAME                    UPD INS DEL
------------------------------ --- --- ---
CNY#                           NO  YES NO
RECORD#                        NO  YES NO
STATUS                         NO  YES NO
TIMEUNIT                       NO  YES NO
---------------------------------------------------------

The above is what you got with the PKs defined. But When I did the same, I get the following.

See, both of us have the NVL.

==========================================================
CREATE OR REPLACE VIEW V_myView AS 
SELECT A.CNY#,A.RECORD#, A.STATUS, A.TIMEUnit 
FROM myTable A, mySlb slb, myUSERPROF u1 
where a.cny# = slb.cny# 
and nvl(a.location#,0) = slb.locationkey 
and slb.SESSIONKEY = sys_context('userenv','SESSION_USER') 
and slb.CNY# = U1.CNY# 
and slb.SESSIONKEY = U1.SESSION# ; 


SQL> select column_name,UPDATABLE,
  2         insertable,deletable
  3  from user_updatable_columns
  4  where table_name = 'V_MYVIEW'
  5  /

COLUMN_NAME                    UPD INS DEL
------------------------------ --- --- ---
CNY#                           NO  NO  NO
RECORD#                        NO  NO  NO
STATUS                         NO  NO  NO
TIMEUNIT                       NO  NO  NO
=========================================================

Tom Kyte
August 31, 2002 - 5:10 pm UTC

Interesting, looks like a bug in the data in the view -- it is definitely NOT insertable (as my example shows). didn't notice it the first time.

I can sort of reproduce it on my system. If I drop my account and start fresh -- I cannot. Must be some cached information that is mucking it up.

In any case -- with NVL, not insertable (without instead of trigger)

Just one more question.

Prince, August 31, 2002 - 5:15 pm UTC

I will do a bench mark with logon trigger.

But, mean time have you noticed much difference in the performance on insert/update/delete trigger on the view?

Thanks,

Tom Kyte
August 31, 2002 - 7:07 pm UTC

The view with the IN will definitely perform better then the join view in this case.

anytime, ANYTIME, you can avoid procedural code -- do it. This seems like a clear cut choice -- since the two are 100% equivalent result wise but semanitically different as far as we are concerned, why would you even consider the join view if your goal is modification?

I am sorry! I meant "after insert trigger"

Prince, August 31, 2002 - 6:15 pm UTC


again got it wrong. It is "INSTEAD of trigger"

Prince, August 31, 2002 - 6:16 pm UTC


Tom Kyte
August 31, 2002 - 7:12 pm UTC

I knew what you meant ;)

A similar view was used in another complex query and was taking lot of time

Prince., August 31, 2002 - 7:24 pm UTC

I have a similar (infact the same query with more columns and different table name) and was used in another complex query.

It was taking lot of time to execute (49 seconds). After I just changed the view definition's in with EXISTS (there was absolutely no other changes), the complex query was taking less than a second.

Thats the reason, I wanted to test different scenarios.

Even, the select same as,

"Select * from v_myview_withIN where cny# = ?" was taking considerably more time than (50 to 60%)
"select * from v_myview_withEsists".

Yes, I would prefer to keep it simple without going thru the trigger.

Thanks again.

Tom Kyte
August 31, 2002 - 7:28 pm UTC

There is a huge difference between IN and EXISTs.

there should be no difference between IN and JOIN as an IN is a JOIN.


Updateable View: Different behaviour in Production Database and Development Database

Yogeeraj, February 20, 2003 - 7:57 am UTC

Hello,

Please allow me to seek some guidance on how to troubleshoot this strange problem where my updateable view is not behaving on my development database and production database alike. thank you in advance

Best Regards
Yogeeraj

Below my homework:
========================================================================================================================
swprod@PROD.YD.MU> CREATE OR REPLACE VIEW V_EMT100_yd
AS (select emt100.emchcod, emt100.ordnum, emt100.gmtseq, emt100.pancod,
emt100.lcncod, emt100.mstatus, ort100.sshpdat
from emt100, ort100
where emt100.ordnum = ort100.ordnum);

View created.

Elapsed: 00:00:00.13
swprod@PROD.YD.MU>
swprod@PROD.YD.MU> update v_emt100_yd
set mstatus = 'Y' where ordnum = '000409C';
set mstatus = 'Y' where ordnum = '000409C'
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table


Elapsed: 00:00:00.03
swprod@PROD.YD.MU> @connect swprod@dev
Enter password:
Connected.
swprod@DEV.YD.MU> CREATE OR REPLACE VIEW V_EMT100_yd
AS (select emt100.emchcod, emt100.ordnum, emt100.gmtseq, emt100.pancod,
emt100.lcncod, ort100.sshpdat, mstatus
from emt100, ort100
where emt100.ordnum = ort100.ordnum);

View created.

Elapsed: 00:00:00.05
swprod@DEV.YD.MU> update v_emt100_yd
set mstatus = 'Y' where ordnum = '000409C';

9 rows updated.

Elapsed: 00:00:00.07
swprod@DEV.YD.MU> rollback;

Rollback complete.

Elapsed: 00:00:00.01
swprod@DEV.YD.MU>

Data in both in my Development database (dev.yd.mu) and Production Database are the same (prod.yd.mu).
==========================================================================

swprod@DEV.YD.MU> select ordnum, sshpdat
2 from ort100
3 where ordnum='000409C';

ORDNUM SSHPDAT
________ __________
000409C 15/03/2001

Elapsed: 00:00:00.01
swprod@DEV.YD.MU> select emchcod, ordnum, gmtseq, pancod, lcncod, mstatus
2 from emt100
3 where ordnum='000409C';

EMCH ORDNUM G PAN LCN M
____ ________ _ ___ ___ _
PT04 000409C B FTR NPX Y
PB01 000409C B FTL NPX Y
PB01 000409C B FTR NPX Y
PT04 000409C B FTL NPX Y
PB01 000409C A FT NPX Y
PB01 000409C A FTL NPX Y
PB01 000409C A FTR NPX Y
PB03 000409C B FTL NPX Y
PT01 000409C A FT NPX Y

9 rows selected.

Elapsed: 00:00:00.03
swprod@DEV.YD.MU>

swprod@DEV.YD.MU> @connect swprod@prod
Enter password:
Connected.
swprod@PROD.YD.MU> select ordnum, sshpdat
from ort100
where ordnum='000409C';

ORDNUM SSHPDAT
________ __________
000409C 15/03/2001

Elapsed: 00:00:00.00
swprod@PROD.YD.MU> select emchcod, ordnum, gmtseq, pancod, lcncod, mstatus
from emt100
where ordnum='000409C';

EMCH ORDNUM G PAN LCN M
____ ________ _ ___ ___ _
PT04 000409C B FTR NPX Y
PB01 000409C B FTL NPX Y
PB01 000409C B FTR NPX Y
PT04 000409C B FTL NPX Y
PB01 000409C A FT NPX Y
PB01 000409C A FTL NPX Y
PB01 000409C A FTR NPX Y
PB03 000409C B FTL NPX Y
PT01 000409C A FT NPX Y

9 rows selected.

Elapsed: 00:00:00.04

swprod@PROD.YD.MU> select index_name, status from user_indexes
2* where table_name in ('EMT100','ORT100')

INDEX_NAME STATUS
______________________________ ________
IND_ORT100_1 VALID
IND_ORT100_2 VALID
IND_ORT100_3 VALID
PK_EMT100 VALID
PK_ORT100 VALID

5 rows selected.

Elapsed: 00:00:00.03

swprod@PROD.YD.MU> select substr(column_name,1,10) col, column_position, index_name from user_
ind_columns
2* where table_name in ('EMT100','ORT100')

COL COLUMN_POSITION INDEX_NAME
__________ _______________ ______________________________
EMCHCOD 1 PK_EMT100
ORDNUM 2 PK_EMT100
GMTSEQ 3 PK_EMT100
PANCOD 4 PK_EMT100
SSHPDAT 1 IND_ORT100_1
EKNTDAT 1 IND_ORT100_2
EDYEDAT 1 IND_ORT100_3
ORDNUM 1 PK_ORT100

8 rows selected.

Elapsed: 00:00:00.03
swprod@PROD.YD.MU> @connect swprod@dev
Enter password:
Connected.

swprod@DEV.YD.MU> select index_name, status from user_indexes
2* where table_name in ('EMT100','ORT100')

INDEX_NAME STATUS
______________________________ ________
IND_ORT100_1 VALID
IND_ORT100_2 VALID
IND_ORT100_3 VALID
PK_EMT100 VALID
PK_ORT100 VALID

5 rows selected.

Elapsed: 00:00:00.05

swprod@DEV.YD.MU> select substr(column_name,1,10) col, column_position, index_name from user_ind_columns
2 where table_name in ('EMT100','ORT100')

COL COLUMN_POSITION INDEX_NAME
__________ _______________ ______________________________
EMCHCOD 1 PK_EMT100
ORDNUM 2 PK_EMT100
GMTSEQ 3 PK_EMT100
PANCOD 4 PK_EMT100
ORDNUM 1 PK_ORT100
SSHPDAT 1 IND_ORT100_1
EKNTDAT 1 IND_ORT100_2
EDYEDAT 1 IND_ORT100_3

8 rows selected.

Elapsed: 00:00:00.04
swprod@DEV.YD.MU>


________________
PS. Thanks a lot for the latest changes on this site. Helps a lot. Maybe if you could post only the latest information on top...


Tom Kyte
February 20, 2003 - 8:24 am UTC 

I'll be like the RBO today -- bottom up.  what do you mean by "if you could post only the latest information on top..."?

Now for the view -- perhaps you used a DEFERRABLE constraint in production?  Set up a test case like this for me to see the creates and all:

ops$tkyte@ORA817DEV> create table emt100 ( emchcod int, ordnum int, gmtseq int, pancod int, lcncod int, mstatus int );

Table created.

ops$tkyte@ORA817DEV> create table ort100 (sshpdat int, ordnum int constraint ort100_Pk PRIMARY KEY );

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> CREATE OR REPLACE VIEW V_EMT100_yd
  2  AS (select emt100.emchcod, emt100.ordnum, emt100.gmtseq, emt100.pancod,
  3   emt100.lcncod, emt100.mstatus, ort100.sshpdat
  4     from emt100, ort100
  5     where emt100.ordnum = ort100.ordnum);

View created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> update v_emt100_yd
  2  set mstatus = 'Y' where ordnum = '000409C';

0 rows updated.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> drop table emt100;

Table dropped.

ops$tkyte@ORA817DEV> drop table ort100;

Table dropped.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create table emt100 ( emchcod int, ordnum int, gmtseq int, pancod int, lcncod int, mstatus int );

Table created.

ops$tkyte@ORA817DEV> create table ort100 (sshpdat int, ordnum int constraint <b>ort100_Pk PRIMARY KEY deferrable );</b>

Table created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> CREATE OR REPLACE VIEW V_EMT100_yd
  2  AS (select emt100.emchcod, emt100.ordnum, emt100.gmtseq, emt100.pancod,
  3   emt100.lcncod, emt100.mstatus, ort100.sshpdat
  4     from emt100, ort100
  5     where emt100.ordnum = ort100.ordnum);

View created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> update v_emt100_yd
  2  set mstatus = 'Y' where ordnum = '000409C';
set mstatus = 'Y' where ordnum = '000409C'
    *
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Yes, you are right! Deferrable constraint in the production database

Yogeeraj, February 21, 2003 - 7:00 am UTC

hi,

Yes, the PK constraints were deferrable in the production database as you correctly mentioned.

===========================================================
===============================================================================
swprod@DEV.YD.MU> select substr(table_name,1,15),
2 substr(constraint_name,1,15),
3 constraint_type,
4 deferrable,
5 deferred,
6 validated,
7 rely
8 from user_constraints
9* where table_name in ('ORT100','EMT100')

SUBSTR(TABLE_NA SUBSTR(CONSTRAI C DEFERRABLE DEFERRED VALIDATED RELY
_______________ _______________ _ ______________ _________ _____________ ____
EMT100 PK_EMT100 P NOT DEFERRABLE IMMEDIATE VALIDATED
EMT100 FK_EMT100_1 R NOT DEFERRABLE IMMEDIATE VALIDATED
EMT100 NN_EMT100_1 C NOT DEFERRABLE IMMEDIATE VALIDATED
EMT100 NN_EMT100_2 C NOT DEFERRABLE IMMEDIATE VALIDATED
EMT100 NN_EMT100_3 C NOT DEFERRABLE IMMEDIATE VALIDATED
EMT100 NN_EMT100_4 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_1 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_2 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_4 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_5 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_6 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_7 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_8 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_9 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_10 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_11 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_12 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_13 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_14 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 PK_ORT100 P NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_1 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_2 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_3 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_4 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_5 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_6 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_7 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 NN_ORT100_1 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 NN_ORT100_2 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 NN_ORT100_3 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 NN_ORT100_4 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 NN_ORT100_5 C NOT DEFERRABLE IMMEDIATE VALIDATED

32 rows selected.

Elapsed: 00:00:00.08
swprod@DEV.YD.MU> @connect swprod@prod
Enter password:
Connected.

swprod@PROD.YD.MU> select substr(table_name,1,15),
2 substr(constraint_name,1,15),
3 constraint_type,
4 deferrable,
5 deferred,
6 validated,
7 rely
8 from user_constraints
9* where table_name in ('ORT100','EMT100')

SUBSTR(TABLE_NA SUBSTR(CONSTRAI C DEFERRABLE DEFERRED VALIDATED RELY
_______________ _______________ _ ______________ _________ _____________ ____
EMT100 PK_EMT100 P DEFERRABLE IMMEDIATE VALIDATED RELY
EMT100 FK_EMT100_1 R NOT DEFERRABLE IMMEDIATE VALIDATED
EMT100 NN_EMT100_EMCHC C NOT DEFERRABLE IMMEDIATE VALIDATED
EMT100 NN_EMT100_ORDNU C NOT DEFERRABLE IMMEDIATE VALIDATED
EMT100 NN_EMT100_GMTSE C NOT DEFERRABLE IMMEDIATE VALIDATED
EMT100 NN_EMT100_PANCO C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 PK_ORT100 P DEFERRABLE IMMEDIATE VALIDATED RELY
ORT100 FK_ORT100_1 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_2 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_3 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_4 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_5 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_6 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 FK_ORT100_7 R NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 NN_ORT100_ORDNU C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 NN_ORT100_CLICO C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 NN_ORT100_ORDDA C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 NN_ORT100_MRCID C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 NN_ORT100_ORDST C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_1 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_2 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_4 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_5 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_6 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_7 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_8 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_9 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_10 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_11 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_12 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_13 C NOT DEFERRABLE IMMEDIATE VALIDATED
ORT100 CK_ORT100_14 C NOT DEFERRABLE IMMEDIATE VALIDATED

32 rows selected.

Elapsed: 00:00:00.09
swprod@PROD.YD.MU>
===============================================================================

Hence, i will have to re-create the Primary Key constraints as the documentation clearly states:
<quote>
Restrictions:

You cannot defer a NOT DEFERRABLE constraint with the SET CONSTRAINT(S) statement.

You cannot specify either DEFERRABLE or NOT DEFERRABLE if you are modifying an existing constraint directly (that is, by specifying the ALTER TABLE ... MODIFY constraint statement).

You cannot alter a constraint's deferrability status. You must drop the constraint and re-create it.

</quote>


But why is the deferrable constraint preventing me from updating the view? I cannot undestand the logic behind it. Please clarify.

Best Regards
Yogeeraj

PS. Sorry for the confusion, i was referring to the home page of ASKTOM. Where the "message" is not "visible" (have to scroll down) on browsers on PCs configured on 800x600 pixel displays... ;) Also, i believe everyone would have loved to have the option where they could "register" themselves to be notified by email on any particular updates to questions.... Thanks

Tom Kyte
February 21, 2003 - 10:08 am UTC

because the primary key created an index and that index is NOT unique hence the update of the join can be ambigous -- the result of the update of the SAME EXACT DATA could be different depending on the physical "order" of the data in the database.

The table joined to must be joined to by a key that is assured always to be unique -- with a deferrable pk constraint -- this is no longer true.


I understand the message thing now, I find it more useful to have the search and list at the top with the messages at the bottom.

I don't want to get into the "email" thing really -- I get enough bounced emails as it it. An automatic system like that scares me. Everytime I touch a question that is older then a couple of weeks -- I get a bounced email from the original submitter as their hotmail/yahoo/whatever account is over quota, cancelled whatever.

Ignore bounced emails

A reader, February 21, 2003 - 1:56 pm UTC


Tom Kyte
February 21, 2003 - 3:21 pm UTC

easy for you to say -- them, er, come to me you see.

How about this -- you give me your email address and I'll just set up a rule that forwards them to you -- OK?

I'll wait for the email address.

please clarify further

Yogeeraj, February 22, 2003 - 1:16 am UTC

hello,

This is quite difficult to visualize. Maybe a white board would have helped!
=======================================
sQL> @connect swprod@pfs
Enter password:
Connected.
swprod@PROD.YD.MU> select ordnum
2 from ort100
3 having count(*) > 1
4 group by ordnum;

no rows selected

Elapsed: 00:00:00.45
swprod@PROD.YD.MU> @connect swprod@dev
Enter password:
Connected.

swprod@DEV.YD.MU> select ordnum
2 from ort100
3 having count(*) > 1
4* group by ordnum

no rows selected

Elapsed: 00:00:00.78
swprod@CMTDB.CMT.MU>

======================================

Despite the Deferrable PK constraint and the index being UNIQUE (from query above), we still have the problem. Is it that "this is not possible when you have deferrable pk constraints"?

thank you in advance for your usual patience and precious time.

best regards
yogeeraj

PS. I can undestand the pain you endure in managing this site especially with all those email addresses that bounce! I hate that too! Anyway, thanks a lot for the latest improvements - i love the new feature "Your Questions".



Tom Kyte
February 22, 2003 - 10:46 am UTC 

the index is NOT unique for a deferrable constraint, that is the problem. You never showed us a "unique" constraint above.


look:


ops$tkyte@ORA920> create table t ( x int primary key deferrable );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> set constraints all deferred;

Constraint set.

ops$tkyte@ORA920> insert into  t select 1 from all_users;

47 rows created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select x from t group by x having count(*) > 1;

         X
----------
         1


See -- i have 47 rows in that table with the number 1 in the primary key.  The constraint check is DEFERRED until commit time.  So when I commit:

ops$tkyte@ORA920> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (OPS$TKYTE.SYS_C007468) violated


See -- there can be dups in there since the constraint is deferrable.  They are not allowed to persist AFTER a commit, the data integrity is checked at transaction commit time instead of after each statement.

now clear

Yogeeraj, February 24, 2003 - 5:25 am UTC

hello,

I guess this explains that...
============================================================
swprod@DEV.YD.MU> @connect swprod@prod
Enter password:
Connected.

swprod@PROD.YD.MU> select index_name, uniqueness
2 from user_indexes
3 where table_name in ('EMT100','ORT100')
4 and index_name like 'PK%';

INDEX_NAME UNIQUENES
______________________________ _________
PK_EMT100 NONUNIQUE
PK_ORT100 NONUNIQUE

Elapsed: 00:00:00.02
swprod@PROD.YD.MU> @connect swprod@dev
Enter password:
Connected.
swprod@DEV.YD.MU> select index_name, uniqueness
2 from user_indexes
3 where table_name in ('EMT100','ORT100')
4* and index_name like 'PK%'

INDEX_NAME UNIQUENES
______________________________ _________
PK_EMT100 UNIQUE
PK_ORT100 UNIQUE

Elapsed: 00:00:00.02
swprod@DEV.YD.MU>
============================================================

I believe, i will have to drop and recreate all my PKs and FKs to be able to use updateable views.

i.e. (at the same time reducing performance degradation time)
a. create index with same columns and position as in the PK.
b. Drop FK constraints
b. Drop PK constraints
c. Create PK constraints with rely option.
d. create FK constraints.

thank you a lot.

Best Regards
Yogeeraj

related issue with JDBC

Andy, October 25, 2004 - 11:03 am UTC

Tom,

I don't understand the following behaviour:

mires@WS2TEST> create table x (rownumber number, mytext varchar2(10));

Tabelle wurde angelegt.

mires@WS2TEST> alter table x add constraint pk_x primary key (rownumber) using index;

Tabelle wurde geändert.

mires@WS2TEST> insert into x values (1,'something');

1 Zeile wurde erstellt.

mires@WS2TEST> update (select t.rownumber, t.mytext from (select rownumber, mytext from x where rownumber = 1 order by rownumber) t where rownum <= 250) set mytext = 'xxxxx';

FEHLER in Zeile 1:
ORA-01732: Datenmanipulationsoperation auf dieser View nicht zulässig

mires@WS2TEST> update (select rownumber, mytext from x where rownumber = 1 order by rownumber) set mytext = 'xxxxx';

1 Zeile wurde aktualisiert.

I'm using this indirect syntax as I'm trying to mimic a problem with using an updateable ResultSet in JDBC. Why does the introduction of:

select t.* (...) t where rownum < 250

to my query cause the update to fail?

Tom Kyte
October 25, 2004 - 11:14 am UTC

order by and rownum are not going to work like that. that view is not "safely updateable". it could update a different set of 250 rows every time it is run!


think about it -- it is not deterministic, hence it is not permitted.


you are getting all rows for rownumber = 1, sorting by rownumber (eg: scramble the data up in any order basically) -- and then taking the first 250 and updating them.

The very next time you run that, on the same data -- it could hit a totally different set of rows, totally ambigous -- totally unsafe, so it will not do it.

Andy, October 25, 2004 - 11:42 am UTC

Thank you for your prompt reply, Tom. My example wasn't the greatest, though, as I am WHERE-ing and ORDER-ing on my primary key (so I'll only ever get one row). If I had this instead:

select * from (select rownumber, mytext from x where mytext like 'a%' order by rownumber) where rownum <= 250

then I'd be getting a bunch of rows ordered by my primary key, and taking the first 250 of them. That could hit different rows each time if the data changes, but it would get the same result for the same underlying data, surely?

Tom Kyte
October 25, 2004 - 12:01 pm UTC

because of the primary key -- yes, in general (without a primary key) no.

in any case, that syntax is not going to be updatable.

Manjunath, February 09, 2006 - 11:04 am UTC

This is related to ORA-01732 error:

Case 1:
=======
Do a full export and import from source to target. All MVs are created with no error.


Case 2:
=========
E1.Export full db with rows=n from source (only metadata).
E2.Export all schemas seperately from source with rows=y.

I1. Import E1 on target to create all metadata.
I2. Import all schemas paralalley.

We get this error for 10% of the MVs:

. . importing table "ISC_DBI_CFM_002_MV" 386 rows imported
. . importing table "ISC_DBI_CFM_003_MV"
Note: table contains ROWID column, values may be obsolete
IMP-00058: ORACLE error 1732 encountered
ORA-01732: data manipulation operation not legal on this view
. . importing table "ISC_DBI_CFM_004_MV" 1053 rows imported
. . importing table "ISC_DBI_CFM_005_MV" 229 rows imported
. . importing table "ISC_DBI_CFM_006_MV"
Note: table contains ROWID column, values may be obsolete
IMP-00058: ORACLE error 1732 encountered
ORA-01732: data manipulation operation not legal on this view
. . importing table "ISC_DBI_CFM_007_MV"
IMP-00058: ORACLE error 1732 encountered
ORA-01732: data manipulation operation not legal on this view
. . importing table "ISC_DBI_CFM_008_MV"
IMP-00058: ORACLE error 1732 encountered
ORA-01732: data manipulation operation not legal on this view
. . importing table "ISC_DBI_CFM_009_MV" 0 rows imported
. . importing table "ISC_DBI_CFM_010_MV"
IMP-00058: ORACLE error 1732 encountered
ORA-01732: data manipulation operation not legal on this view
. . importing table "ISC_DBI_CFM_011_MV" 0 rows importe


Case 3:
=========
E1.Export full db with rows=n from source (only metadata).
E2.Export all schemas seperately from source with rows=y.

I1. Import E1 on target to create all metadata.
Disable all Foreign keys.
I2. Import all schemas paralalley.

We get this error for 10% of the MVs(similar to Case 2):

. . importing table "ISC_DBI_CFM_002_MV" 386 rows imported
. . importing table "ISC_DBI_CFM_003_MV"
Note: table contains ROWID column, values may be obsolete
IMP-00058: ORACLE error 1732 encountered
ORA-01732: data manipulation operation not legal on this view
. . importing table "ISC_DBI_CFM_004_MV" 1053 rows imported
. . importing table "ISC_DBI_CFM_005_MV" 229 rows imported
. . importing table "ISC_DBI_CFM_006_MV"
Note: table contains ROWID column, values may be obsolete
IMP-00058: ORACLE error 1732 encountered
ORA-01732: data manipulation operation not legal on this view
. . importing table "ISC_DBI_CFM_007_MV"
IMP-00058: ORACLE error 1732 encountered
ORA-01732: data manipulation operation not legal on this view
. . importing table "ISC_DBI_CFM_008_MV"
IMP-00058: ORACLE error 1732 encountered
ORA-01732: data manipulation operation not legal on this view
. . importing table "ISC_DBI_CFM_009_MV" 0 rows imported
. . importing table "ISC_DBI_CFM_010_MV"
IMP-00058: ORACLE error 1732 encountered
ORA-01732: data manipulation operation not legal on this view
. . importing table "ISC_DBI_CFM_011_MV" 0 rows importe



Any reasons :
1)why we get this and
2)how to resolve this for parallel export/import

Manjunath

A reader, February 09, 2006 - 11:08 am UTC

Details for previous update:

Source version: 9.2.0.6.0
Target Version: 9.2.0.6

There is a bug related to ORA-01732 for import where this error comes if the MV is already existing and we try to import it. But in our case this error comes even when that MV is not present in the target (before import)

A reader, September 21, 2006 - 5:52 am UTC


Updateable View

Kishore, March 08, 2007 - 10:04 am UTC

Hello Sir,

Could you please let me know why would the below update fail

SQL>
1 UPDATE (SELECT * FROM (SELECT * FROM emp2 ORDER BY empno DESC) WHERE ROWNUM
= 1)
2* SET sal = sal + 1000
SQL> /
UPDATE (SELECT * FROM (SELECT * FROM emp2 ORDER BY empno DESC) WHERE ROWNUM = 1)

*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

Tom Kyte
March 08, 2007 - 11:11 am UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1081632740728#156125200346772086

ora-01732 and instead of trigger

dk, April 29, 2008 - 10:49 am UTC

Hello Tom,
I have a problem with updating views. I've read a little documentation and yours valuables answers on this site but still don't understand. My example is:

SQL> create table taba as
  2  select object_id, object_name, object_type
  3  from user_objects
  4  where object_id is not null;

Table created.

SQL> alter table taba modify object_id not null;

Table altered.

SQL> alter table taba add constraint pk_taba primary key (object_id);

Table altered.

SQL> select table_name, column_name, updatable, insertable, deletable
  2  from user_updatable_columns
  3  where table_name = 'TABA';

TABLE_NAME COLUMN_NAME UPD INS DEL
---------- ----------- --- --- ---
TABA       OBJECT_ID   NO  NO  NO
TABA       OBJECT_NAME NO  NO  NO
TABA       OBJECT_TYPE NO  NO  NO

SQL> update taba set object_id = object_id + 1;

1549 rows updated.


First question:
Why select from user_updatable_columns shows "NO" but I can update table taba?

SQL> create table tabb as
  2  select rownum column_id, column_name, object_id table_id, object_type, data_type test, sysdate mod
  3  from user_tab_columns, taba
  4  where object_type in ('TABLE', 'VIEW')
  5  and table_name = object_name;

Table created.

SQL> alter table tabb add constraint pk_tabb primary key (column_id);

Table altered.

SQL> alter table tabb add constraint fk_tabb_taba foreign key (table_id) references taba (object_id);

Table altered.

SQL> create or replace view via as select * from taba where object_type = 'TABLE';

View created.

SQL> create or replace view vib as select * from tabb where object_type = 'TABLE';

View created.

SQL> update (select * from via, vib where table_id = object_id) set test = 1;

2774 rows updated.

SQL> create or replace trigger tr_vib_iu instead of update on vib for each row
  2  begin
  3    update tabb set mod = sysdate where column_id = :new.column_id;
  4  end;
  5  /

Trigger created.

SQL> update (select * from via, vib where table_id = object_id) set test = 2;
update (select * from via, vib where table_id = object_id) set test = 2
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


Second question:
Why I cannot update after creating trigger? We have similar situation in our database where we use views for all the tables and three trigger "instead of" for every view.

Could you please explain the behavior above? Thank you in advance.
Tom Kyte
April 29, 2008 - 11:10 am UTC

q1) because taba is a table, not a view.

q2) you have a cartesian join there - do you know what you are doing there?

do you see that your trigger is modifying the table it is being fired on in the first place?? this makes no sense to me, I cannot understand the real world use case here.


We have similar situation in our database where we use views for all the tables and three trigger "instead of" for every view.


that is scary sounding.

ora-01732 continued

dk, April 30, 2008 - 2:29 am UTC

Tom,
Thank you for prompt response.

q1) So user_updatable_columns isn't suitable for tables? Is it possible that I cannot update the table anyway? Select for the second table gives different answer:
SQL> select table_name, column_name, updatable, insertable, deletable
  2  from user_updatable_columns
  3  where table_name = 'TABB';

TABLE_NAME COLUMN_NAME UPD INS DEL
---------- ----------- --- --- ---
TABB       COLUMN_ID   YES YES YES
TABB       COLUMN_NAME YES YES YES
TABB       TABLE_ID    YES YES YES
TABB       OBJECT_TYPE YES YES YES
TABB       TEST        YES YES YES
TABB       MOD         YES YES YES

6 rows selected.


q2)
you have a cartesian join there - do you know what you are doing there?
 
update (select * from via, vib where table_id = object_id) set test = 2;

I know that I update all the rows (example is primitive) but where is the cartesian product?

do you see that your trigger is modifying the table it is being fired on in the first place?? this makes no sense to me, I cannot understand the real world use case here.
Trigger body is not the case (to get ora-01732). I've tried:
SQL> create or replace trigger tr_vib_iu instead of update on vib for each row
  2  declare
  3    x number;
  4  begin
  5    x := 1;
  6  end;
  7  /

Trigger created.

SQL> update (select * from via, vib where table_id = object_id) set test = 2;
update (select * from via, vib where table_id = object_id) set test = 2
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


Documentation says:
These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

We inherited our built-in history model from other application. In this model updating a row causes insert a copy from before change. We've created a view for every table and triggers "instead of update". All operations are performed on views. Is there a way to achieve this in normal (on table) trigger?
Tom Kyte
April 30, 2008 - 10:17 am UTC

Ok, i read this wrong:

select * from via, vib where table_id = object_id

you threw me off with the reference to table_id and object_id -



but the error is being raised for the same/similar reason as "table is not key preserved" would be in most cases.

it would not be deterministic since we don't know how many times a given row in VIB will be returned in the query. (in this case, we do actually, but it isn't 'smart' enough to see that, it is taking the safe course of action)


... Is there a way to achieve this in normal (on table) trigger?
...

ummm, yes, you have after insert or update or delete for each row triggers on tables.

after update trigger

dk, May 07, 2008 - 3:08 am UTC 

Hello Tom,
Thanks for your answer. I was on a little vacation. 
<i>ummm, yes, you have after insert or update or delete for each row triggers on tables.</i>
I cannot get it work because of ORA-04091 (the table is mutating). Maybe my description wasn't clear enough. I try with pseudo-code:
<code>
create table T (col1, col2, ... colN, sysdt DATE NULL);

create view V as select col1, ... colN from T where sysdt is null;

create instead of update trigger on V for each row
begin
  perform update on T;
  insert to T a copy of updating record with old values of 
    col1, ... colN and sysdt = SYSDATE;
end;

Application uses view V to read and write table T. When updating a copy is saved on the same table. We don't use triggers with tables that changes very often. But that mechanism gives us whole history for strategic tables.
Is there a way to replace 'instead of' triggers? Or should we think of modify our approach to record history?</code>
Tom Kyte
May 08, 2008 - 2:31 am UTC

looks like you are trying to maintain a history, why would you not just use table versioning? See the application developers guide for workspace manager - search this site for dbms_wm for examples. We already do this (and more)

workspace manager

dk, May 12, 2008 - 2:05 am UTC

Tom,
It's optimistic that we choose the right way to maintain inherited history model (views and "instead of" triggers) in our solution - the same as Oracle in version-enable tables. I'm going to learn more about workspace manager and test it. Anyway, it's not possible for us to use it straight away.
We appreciate your help.

Creating a View

A reader, November 12, 2008 - 3:15 pm UTC

Hi Tom,

We have a table which is at present 400G as asset catalog. It recieves one record as input record creates 7-8 tmp records at each workflow stage before finally creating the output record.

We are thinking to get the temporary records generated during each stage into another table and impose Partitioning and reatin only last 10 days of data.

60% of this table is TMP records which does have no meaning to be there in this table.

We are thinking two create two tables T and T_TMP,
T will have input and output record
T_TMP will have 7-8 records based on workflow stages.

Application will see them as a VIEW comprised of T UNION ALL T_TMP.

Can the application insert/update on a VIEW as it will be very complex to code and change the application at this point of time.

Your valuable suggestions are appreciated
Tom Kyte
November 13, 2008 - 4:55 pm UTC

is there any way to concretely identify T versus T_TMP records

and why wouldn't you use partitioning for that as well as range? We would range partition (for 10 days requirement) and probably list partition for the T versus T_TMP stuff.

Updateable View using UNION ALL

A reader, November 13, 2008 - 1:40 pm UTC

Hi Tom,

Can you please answer the above question.

Thanks much!
Tom Kyte
November 14, 2008 - 4:48 pm UTC

I've written a thousand times perhaps...

I don't see every review followup
They are supposed to be reviews (that is, not questions)
I don't spend 24 hours a day doing this. It only feels like it.

T Vs T_Tmp

A reader, November 14, 2008 - 10:52 am UTC

The difference between T and T_Tmp is that T will have a column named _type with Values 'Input','Output' where as the table T_Tmp will not have that column since all records will have File_type as 'Temp'.

We are not considering to range partition since there will be too many partitions (more than 5 yrs of data ~1800 partitions)to hold and local indexes at that level doesn't give that optimal performance.

So we are considering T to be a regular heap table and regular B*tree indexes and consider T_TMP as a range based partition table where in the temp records will be dropped after the workflow gets finished, and we have defined the retention to be a max of 10 days for a workflow to get finished.

I searched some stuff and it seems that UNION ALL doesn't become an updateable view but going my your thougts in these forums the INSTEAD of triggers can do the MAGIC.

I have Never used INSTEAD Of trigger and hope you can illustriate some example on the above case to take it forward.

Hope some help from you.
Tom Kyte
November 18, 2008 - 6:08 pm UTC

...
We are not considering to range partition since there will be too many
partitions (more than 5 yrs of data ~1800 partitions)to hold and local indexes
at that level doesn't give that optimal performance
....

then use global indexes. partition.

done. indexes can be maintained during partition operations, index the way you need, use partitioning

SPOOL

Rajeshwaran Jeyabal, November 14, 2008 - 5:00 pm UTC

I just want to know how to get the column names in spool file.
Normally, whenver we use spool file for listing the selected records, that will contain column name and corresponding data.
But, if there is no record for a particular select query, the spool file only contains "no rows selected".
Here is my question. Can we get the column names under which "no rows selected" message???
For example: A table contain 2 columns and i am running select query for a particular condn. it doesn't contain any record.
column1 column2
no rows selected.
instead of only "no rows selected".
Please help me on this.
Tom Kyte
November 18, 2008 - 6:19 pm UTC

sqlplus doesn't really work that way.

is there a really inefficient way to write a query to make sure it returns at least one row? sure, but it would be a really bad idea.


If you need this sort of control over the output format, I would suggest, no - I would actually state "you need to use a report writing tool or write a program"

sqlplus is a simple, very very very simple command line interface for ad-hoc queries. It has very very limited reporting capabilities.

Updatable view with Union all

dav, November 04, 2009 - 12:32 pm UTC

Hi Tom,

Thank you for the wonderful site.

Let's say I've two identical tables.

Temp1
field1 number,
field2 varchar2(200)
Temp2
field1 number,
field2 varchar2(200)

I've a view temp_v with following query

create or replace view temp_v as
select * from temp1
union all
select * from temp2

If I want to update this view,for ex.

update temp_v
set field2 = 'Updated'.

I want to update the both the tables using a single statement.

Is this possible? How can I achive this.





Tom Kyte
November 11, 2009 - 9:23 am UTC

you would have to write instead of triggers to do that.

which makes me say "do not write instead of triggers" but rather 'fix your data model'

triggers are magic
magic is bad
triggers are magic


Either update the base tables (straight forward, everyone that reads the code KNOWS what is going on)

or fix the data model - you want a single table apparently. Using partitioning if you need to segregate them physically for some reason, but have a single table.

Why not updatable?

A reader, May 03, 2012 - 9:58 pm UTC

Hi, Tom,

I've a view as：
CREATE OR REPLACE VIEW test
AS
WITH v1 AS
(SELECT f.code,
f.name,
p.code pcode,
p.name pname,
(SELECT 'X' FROM dual
) x
FROM dcf_fund f,
dcf_ab_stock_fund_position p
WHERE f.code=p.code
)
SELECT
(SELECT 1
FROM dual
WHERE dummy=x
) v1,
v1.*
FROM v1

and it's not updatable:
select column_name, updatable
from dba_updatable_columns
where table_name='TEST';
COLUMN_NAME UPDATABLE
------------------------------ ---------
V1 NO
CODE NO
NAME NO
PCODE NO
PNAME NO
START_DATE NO

But, if I:

CREATE OR REPLACE VIEW test
AS
WITH v1 AS
(SELECT f.code,
f.name,
p.code pcode,
p.name pname,
(SELECT 'X' FROM dual
) x
FROM dcf_fund f,
dcf_ab_stock_fund_position p
WHERE f.code=p.code
)
SELECT
(SELECT 1
FROM dual
WHERE dummy='X'
) v1,
v1.*
FROM v1

It's updatable:
COLUMN_NAME UPDATABLE
------------------------------ ---------
V1 NO
CODE YES
NAME YES
PCODE YES
PNAME YES
X NO

And if I:

CREATE OR REPLACE VIEW test
AS
WITH v1 AS
(SELECT f.code,
f.name,
p.code pcode,
p.name pname,
'X' x
FROM dcf_fund f,
dcf_ab_stock_fund_position p
WHERE f.code=p.code
)
SELECT
(SELECT 1
FROM dual
WHERE dummy=x
) v1,
v1.*
FROM v1

It's also updatable:
COLUMN_NAME UPDATABLE
------------------------------ ---------
V1 NO
CODE YES
NAME YES
PCODE YES
PNAME YES
X NO

Why?

Thanks and Regards,
William
Tom Kyte
May 06, 2012 - 2:06 pm UTC

no creates
no look

A reader, May 10, 2012 - 12:36 am UTC

Sorry, The creates are:
CREATE TABLE DCF_FUND
(
"CODE" VARCHAR2(10),
"NAME" VARCHAR2(30),
CONSTRAINT "DCF_FUNDS_PK" PRIMARY KEY ("CODE"),
CONSTRAINT "DCF_FUNDS_UK1" UNIQUE ("NAME")
)
;

CREATE TABLE DCF_AB_STOCK_FUND_POSITION
(
"CODE" VARCHAR2(10),
"NAME" VARCHAR2(20),
"POSITION" NUMBER,
CONSTRAINT "DCF_AB_STOCK_FUND_POSITIO_PK" PRIMARY KEY ("CODE")
)
;
Tom Kyte
May 10, 2012 - 8:37 am UTC

In the first query - you have

(select 1 from dual where dummy = x)

and X is an attribute from the outer query and that attribute comes from the with view and is "select 'X' from dual"

Now, select 'X' from dual might be 'X' or it might be NULL - we don't know until runtime. So, it is truly 'variable', unknown. And that is causing the database to just say "cannot update this"

In the second query, the scalar subquery isn't reliant on anything - it stands alone, it doesn't need the with subquery at all.

The third query is the same as the second for that fact. The database knows that x (the attribute) and 'X' the constant are the same in this case. Even though the third looks like the first - it is more like the second. It optimized the one scalar subquery away - it is really like the second - it is a constant.


A reader, May 11, 2012 - 12:54 am UTC

Why does an unknown variable cause the database to just say "cannot update this"?
I just learn from this thread that the database allow updates to objects in the key preserved table.
And that each row in a table will be returned at most once in the result view will be said as a key preserved table and thus can be updated from the result view.

In my case, whatever the variable from "select 'X' from dual" is null or not, the each row from DCF_FUND and DCF_AB_STOCK_FUND_POSITION will be returned at most once in my test view.
So, they should be key preserved tables and the columns from them must be updatable.

Where am I wrong?

Regards,
William
Tom Kyte
May 11, 2012 - 1:41 am UTC

There are more requirements than just "key preserved"

Key preserved is just one of many requirements.


No one would argue that EMP is not key preserved in a join to DEPT - given the correct primary/foreign keys in place.

ops$tkyte%ORA11GR2> create or replace view v1
  2  as
  3  select emp.empno, emp.ename, dept.deptno, dept.dname
  4    from emp, dept
  5   where emp.deptno = dept.deptno;

View created.

ops$tkyte%ORA11GR2> create or replace view v2
  2  as
  3  select emp.empno, emp.ename, dept.deptno, dept.dname, rownum r
  4    from emp, dept
  5   where emp.deptno = dept.deptno;

View created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> update v1 set ename = ename;

14 rows updated.

ops$tkyte%ORA11GR2> update v2 set ename = ename;
update v2 set ename = ename
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view



You might argue "why isn't ENAME updateable in the second view" - the answer is "it just isn't". The addition of rownum changed everything.

The introduction of your scalar subquery dependent on something made some level of view merging not possible and made the view be marked as not updateable.

You would have to use an instead of trigger (not recommended) to make it updateable.


A reader, May 14, 2012 - 10:56 pm UTC

Thank you!

Could you suggest some documents from oracle or some books discussing this in detail?

Regards,
William
Tom Kyte
May 14, 2012 - 11:58 pm UTC

None that I'm aware of, it is a fairly esoteric, small detail. We document the existance of the "updateable" view - but that is about it. We attempt to make what we can updateable and report to you how much of it is.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library