Re: user_updatable_columns

From: <fitzjarrell_at_cox.net>
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

Original text of this message