Re: user_updatable_columns
Date: Wed, 11 Jun 2008 06:13:59 -0700 (PDT)
Message-ID: <cddce2fa-9a26-4bc8-b7a9-192277b30eed@a70g2000hsh.googlegroups.com>
On Jun 11, 6:57 am, "Tomo" <tkoko..._at_varteks.com> wrote:
> "Shakespeare" <what..._at_xs4all.nl> wrote in message
>
> news:484f9399$0$14353$e4fe514c_at_news.xs4all.nl...
>
>
>
>
>
>
>
> > "Tomo" <tkoko..._at_varteks.com> schreef in bericht
> >news:g2o39c$mr8$1_at_ss408.t-com.hr...
>
> >> "Shakespeare" <what..._at_xs4all.nl> wrote in message
> >>news:484f879d$0$14345$e4fe514c_at_news.xs4all.nl...
>
> >>> "Tomo" <tkoko..._at_varteks.com> schreef in bericht
> >>>news:g2nvi8$e70$1_at_ss408.t-com.hr...
> >>>> if i
> >>>> select * from user_updatable_columns
> >>>> i get information if column in table is updatable or not, insertable or
> >>>> not, deletable or not.
>
> >>>> What makes column updatable, insertable, deletable?? Primary key, not
> >>>> null
> >>>> constraints..??
> >>>> please clear my mind!
>
> >>> USER_UPDATABLE_COLUMNS describes columns in a JOIN VIEW that can be
> >>> updated by the current user.
>
> >> Yes, but why one column is updatable or other is not..is it depend on
> >> create table statement??
>
> >>> Shakespeare
>
> > No it is how your CREATE OR REPLACE VIEW statement is built. It's not
> > about tables, but about views. If you update a view, Oracle must be able
> > to find (uniquely) the underlying row in one of the tables the view was
> > composed of.
>
> if query is written with rowid then every row will be uniquely recognized.
> So if i understand you right every column will be updatable.
>
> for example ..
> create or replace view view_emp_dept as
> select e.*, e.rowid rowemp, d.*, d.rowid rowdept
> from
> emp e, dept d
> where e.deptno = d.deptno
>
> in USER_UPDATABLE_COLUMNS every column from emp and dept could be
> updatable???
>
>
>
>
>
> > Shakespeare- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
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 Received on Wed Jun 11 2008 - 08:13:59 CDT