On Jun 11, 6:57*am, "Tomo" <tkoko... (AT) varteks (DOT) com> wrote:
No:

SQL> create or replace view view_emp_dept as
2 select e.*, e.rowid rowemp, d.*, d.rowid rowdept
3 from
4 emp e, dept d
5 where e.deptno = d.deptno
6 /
select e.*, e.rowid rowemp, d.*, d.rowid rowdept
*
ERROR at line 2:
ORA-00957: duplicate column name


SQL>

You have an ambiguously named column (deptno) in your view. Let's
modify that and see if it works:

SQL> spool user_upd_cols_ex.lst
SQL> create or replace view view_emp_dept as
2 select e.*, e.rowid rowemp, d.deptno depno, d.dname, d.loc,
d.rowid rowdept
3 from
4 emp e, dept d
5 where e.deptno = d.deptno
6 /

View created.

SQL>
SQL> select * From user_updatable_columns
2 where table_name = 'VIEW_EMP_DEPT'
3 /

OWNER TABLE_NAME
COLUMN_NAME UPD INS DEL
------------------------------ ------------------------------
------------------------------ --- --- ---
BING VIEW_EMP_DEPT
EMPNO NO NO NO
BING VIEW_EMP_DEPT
ENAME NO NO NO
BING VIEW_EMP_DEPT
JOB NO NO NO
BING VIEW_EMP_DEPT
MGR NO NO NO
BING VIEW_EMP_DEPT
HIREDATE NO NO NO
BING VIEW_EMP_DEPT
SAL NO NO NO
BING VIEW_EMP_DEPT
COMM NO NO NO
BING VIEW_EMP_DEPT
DEPTNO NO NO NO
BING VIEW_EMP_DEPT
ROWEMP NO NO NO
BING VIEW_EMP_DEPT
DEPNO NO NO NO
BING VIEW_EMP_DEPT
DNAME NO NO NO

OWNER TABLE_NAME
COLUMN_NAME UPD INS DEL
------------------------------ ------------------------------
------------------------------ --- --- ---
BING VIEW_EMP_DEPT
LOC NO NO NO
BING VIEW_EMP_DEPT
ROWDEPT NO NO NO

13 rows selected.

SQL>

And, gee, the answer is still 'No'. Simply including the rowids
doesn't uniquely identify the rows in the base tables, so NONE of the
columns in your view are updatable. Let's add primary key constraints
to both the EMP and DEPT tables and try yet again:

SQL> alter table emp add constraint emp_pk primary key (empno);

Table altered.

SQL>
SQL> alter table dept add constraint dept_pk primary key (deptno);

Table altered.

SQL>
SQL> alter table emp add constraint emp_dept_fk foreign key (deptno)
references dept;

Table altered.

SQL>
SQL> create or replace view view_emp_dept as
2 select e.*, e.rowid rowemp, d.deptno depno, d.dname, d.loc,
d.rowid rowdept
3 from
4 emp e, dept d
5 where e.deptno = d.deptno
6 /

View created.

SQL>
SQL> select * From user_updatable_columns
2 where table_name = 'VIEW_EMP_DEPT'
3 /

OWNER TABLE_NAME
COLUMN_NAME UPD INS DEL
------------------------------ ------------------------------
------------------------------ --- --- ---
BING VIEW_EMP_DEPT
EMPNO YES YES YES
BING VIEW_EMP_DEPT
ENAME YES YES YES
BING VIEW_EMP_DEPT
JOB YES YES YES
BING VIEW_EMP_DEPT
MGR YES YES YES
BING VIEW_EMP_DEPT
HIREDATE YES YES YES
BING VIEW_EMP_DEPT
SAL YES YES YES
BING VIEW_EMP_DEPT
COMM YES YES YES
BING VIEW_EMP_DEPT
DEPTNO YES YES YES
BING VIEW_EMP_DEPT
ROWEMP YES YES YES
BING VIEW_EMP_DEPT
DEPNO NO NO NO
BING VIEW_EMP_DEPT
DNAME NO NO NO

OWNER TABLE_NAME
COLUMN_NAME UPD INS DEL
------------------------------ ------------------------------
------------------------------ --- --- ---
BING VIEW_EMP_DEPT
LOC NO NO NO
BING VIEW_EMP_DEPT
ROWDEPT NO NO NO

13 rows selected.

SQL>
SQL> create or replace view view_emp_dept as
2 select e.empno, e.ename, e.mgr, e.job, e.sal, e.comm, e.hiredate,
d.deptno, d.dname, d.loc
3 from
4 emp e, dept d
5 where e.deptno = d.deptno
6 /

View created.

SQL>
SQL> select * From user_updatable_columns
2 where table_name = 'VIEW_EMP_DEPT'
3 /

OWNER TABLE_NAME
COLUMN_NAME UPD INS DEL
------------------------------ ------------------------------
------------------------------ --- --- ---
BING VIEW_EMP_DEPT
EMPNO YES YES YES
BING VIEW_EMP_DEPT
ENAME YES YES YES
BING VIEW_EMP_DEPT
MGR YES YES YES
BING VIEW_EMP_DEPT
JOB YES YES YES
BING VIEW_EMP_DEPT
SAL YES YES YES
BING VIEW_EMP_DEPT
COMM YES YES YES
BING VIEW_EMP_DEPT
HIREDATE YES YES YES
BING VIEW_EMP_DEPT
DEPTNO NO NO NO
BING VIEW_EMP_DEPT
DNAME NO NO NO
BING VIEW_EMP_DEPT
LOC NO NO NO

10 rows selected.

SQL>
SQL> create or replace view view_emp_dept as
2 select e.empno, e.ename, e.mgr, e.job, e.sal, e.comm, e.hiredate,
e.deptno, d.dname, d.loc
3 from
4 emp e, dept d
5 where e.deptno = d.deptno
6 /

View created.

SQL>
SQL> select * From user_updatable_columns
2 where table_name = 'VIEW_EMP_DEPT'
3 /

OWNER TABLE_NAME
COLUMN_NAME UPD INS DEL
------------------------------ ------------------------------
------------------------------ --- --- ---
BING VIEW_EMP_DEPT
EMPNO YES YES YES
BING VIEW_EMP_DEPT
ENAME YES YES YES
BING VIEW_EMP_DEPT
MGR YES YES YES
BING VIEW_EMP_DEPT
JOB YES YES YES
BING VIEW_EMP_DEPT
SAL YES YES YES
BING VIEW_EMP_DEPT
COMM YES YES YES
BING VIEW_EMP_DEPT
HIREDATE YES YES YES
BING VIEW_EMP_DEPT
DEPTNO YES YES YES
BING VIEW_EMP_DEPT
DNAME NO NO NO
BING VIEW_EMP_DEPT
LOC NO NO NO

10 rows selected.

SQL>
SQL> create or replace view view_emp_dept as
2 select e.empno, e.ename, e.mgr, e.job, e.sal, e.comm, e.hiredate,
e.deptno, d.dname, d.loc
3 from
4 emp e, dept d
5 where d.deptno = e.deptno
6 /

View created.

SQL>
SQL> select * From user_updatable_columns
2 where table_name = 'VIEW_EMP_DEPT'
3 /

OWNER TABLE_NAME
COLUMN_NAME UPD INS DEL
------------------------------ ------------------------------
------------------------------ --- --- ---
BING VIEW_EMP_DEPT
EMPNO YES YES YES
BING VIEW_EMP_DEPT
ENAME YES YES YES
BING VIEW_EMP_DEPT
MGR YES YES YES
BING VIEW_EMP_DEPT
JOB YES YES YES
BING VIEW_EMP_DEPT
SAL YES YES YES
BING VIEW_EMP_DEPT
COMM YES YES YES
BING VIEW_EMP_DEPT
HIREDATE YES YES YES
BING VIEW_EMP_DEPT
DEPTNO YES YES YES
BING VIEW_EMP_DEPT
DNAME NO NO NO
BING VIEW_EMP_DEPT
LOC NO NO NO

10 rows selected.

SQL>

No matter what we do or which constraints we create we can not get the
columns from the DEPT table to be updatable in this view of yours,
since the values from the DEPT table aren't uniquely defined to a
single row of the view. Note, though, that all of the columns from
the EMP table are updatable (and also note that, in your 'original'
view, even the ROWID from the EMP table is updatable).

The presence of the ROWIDs didn't make the EMP columns updatable, it
was the primary key for the EMP table which permitted these columns in
this view to be modifiable.


David Fitzjarrell