Home » SQL & PL/SQL » SQL & PL/SQL » DML through view
DML through view [message #185457] Tue, 01 August 2006 19:03 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

I created a relational view as follows:

CREATE OR REPLACE FORCE VIEW V_EMP
AS
SELECT EMPNO,SAL||','||COMM SAL,SAL*10 SAL1
FROM EMP;

Then i checked USER_UPDATABLE_COLUMNS view

SQL> SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = 'V_EMP';

OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME UPD INS DEL
------------------------------ --- --- ---
RITESH V_EMP
EMPNO YES YES YES

RITESH V_EMP
SAL NO NO NO

RITESH V_EMP
SAL1 NO NO NO


SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO ESSN
---------- ----------
1000 10000
10


SQL> DELETE FROM V_EMP WHERE SAL1 = 100000;

1 row deleted.

SQL> select * from emp;

no rows selected


How it allowed to use to use SAL1 when it is saying that it is not deletable.What is the meaning of output given by USER_UPDATABLE_COLUMNS for each column of a view
Re: DML through view [message #185468 is a reply to message #185457] Tue, 01 August 2006 22:07 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It means the column value can be UPDATEed (using an UPDATE statement). It makes no such promises regarding DELETE.

Ross Leishman
Previous Topic: improve group by query in table with partitions
Next Topic: How to swap data of two colums for same table using only one query
Goto Forum:
  


Current Time: Tue Dec 06 04:35:31 CST 2016

Total time taken to generate the page: 0.10812 seconds