Views are interesting constructs in Oracle. They can be built on a single table, they can contain aggregate data, they can be built on two or more tables joined together, they can even be built on other views. Regardless of all of that, sometimes users may want to update data through a view and, depending upon how the view is built those updates may or may not be allowed. Short of making the attempt and receiving the following dismal message:
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table
how do you know if you can update a particular view? Ask Oracle, of course.
Oracle, in its infinite wisdom, provides three views to reveal which views can be updated, and even which columns in those views have modifiable data. These views are:
USER_UPDATABLE_COLUMNS
ALL_UPDATABLE_COLUMNS
DBA_UPDATABLE_COLUMNS
The level of access in the database determines which view will provide the desired information; the USER-named view reports on all tables/views owned by the connected user, the ALL-named view reports on all tables and views the connected user can access regardless of ownership, and the DBA-named view reports on all tables and views in the database. Usually the USER_UPDATABLE_COLUMNS view should be used; a sample query is shown below:
SQL> select owner, table_name, column_name, updatable
2 from user_updatable_columns;
OWNER TABLE_NAME COLUMN_NAME UPD
------------------------------ ------------------------------ ------------------------------ ---
BING AA CUCD1 YES
BING AA CVRA YES
BING AA CONV_RESULT YES
BING BONUS ENAME YES
BING BONUS JOB YES
BING BONUS SAL YES
BING BONUS COMM YES
BING BONUS DNAME YES
BING CPU_APPLY_VERSION INSTANCE_NAME YES
BING CPU_APPLY_VERSION HOST_NAME YES
BING CPU_APPLY_VERSION VERSION YES
BING CPU_APPLY_VERSION COMMENTS YES
BING CPU_APPLY_VERSION ACTION_TIME YES
BING CPU_APPLY_VERSION_HOLD INSTANCE_NAME YES
BING CPU_APPLY_VERSION_HOLD HOST_NAME YES
BING CPU_APPLY_VERSION_HOLD VERSION YES
BING DBAOBJS OWNER NO
BING DBAOBJS OBJECT_NAME NO
BING DBAOBJS SUBOBJECT_NAME NO
BING DBAOBJS OBJECT_ID NO
BING DBAOBJS DATA_OBJECT_ID NO
BING DBAOBJS OBJECT_TYPE NO
BING DBAOBJS CREATED NO
BING DBAOBJS LAST_DDL_TIME NO
BING DBAOBJS TIMESTAMP NO
BING DBAOBJS STATUS NO
BING DBAOBJS TEMPORARY NO
BING DBAOBJS GENERATED NO
BING DBAOBJS SECONDARY NO
BING DBAOBJS NAMESPACE NO
BING DBAOBJS EDITION_NAME NO
...
This, of course, returns rows for tables and views; restricting this to just the views is a simple task:
SQL> select owner, table_name, column_name, updatable
2 from user_updatable_columns
3 where table_name in (select view_name from user_views);
OWNER TABLE_NAME COLUMN_NAME UPD
------------------------------ ------------------------------ ------------------------------ ---
BING EMP_VIEW EMPNO YES
BING EMP_VIEW ENAME YES
BING EMP_VIEW JOB YES
BING EMP_VIEW MGR YES
BING EMP_VIEW HIREDATE YES
BING EMP_VIEW SAL YES
BING EMP_VIEW COMM YES
BING EMP_VIEW DEPTNO YES
BING V_BASE OBJECT_NAME NO
BING V_BASE SUBOBJECT_NAME NO
BING V_BASE OBJECT_ID NO
BING V_BASE DATA_OBJECT_ID NO
BING V_BASE OBJECT_TYPE NO
BING V_BASE CREATED NO
BING V_BASE LAST_DDL_TIME NO
BING V_BASE TIMESTAMP NO
BING V_BASE STATUS NO
BING V_BASE TEMPORARY NO
BING V_BASE GENERATED NO
BING V_BASE SECONDARY NO
BING V_BASE NAMESPACE NO
BING V_BASE EDITION_NAME NO
BING YINGYONG SNERM NO
BING YINGYONG FLANG NO
BING YINGYONG GLERBIT NO
BING YINGYONG DRONK NO
26 rows selected.
SQL>
In this example it’s possible to reduce the output considerably since all columns in a given view report the same value for UPDATABLE:
SQL> select distinct owner, table_name, updatable
2 from user_updatable_columns
3 where table_name in (select view_name from user_views);
OWNER TABLE_NAME UPD
------------------------------ ------------------------------ ---
BING EMP_VIEW YES
BING V_BASE NO
BING YINGYONG NO
SQL>
Now we know that only the EMP_VIEW is updatable in this schema. Updatable views across schemas can also be found using the following query:
select distinct owner, table_name, updatable
from all_updatable_columns
where table_name in (select view_name from all_views)
and updatable = 'YES';
The list generated by that query may be quite large.
There is another way around the problem of updating data in an otherwise non-updatable view, and that is the INSTEAD OF trigger, which fires instead of the coded action (insert, update, delete) and bypasses the view by performing the requested action on the base tables. And that is a subject for another post.
So, it’s easy to find the views a user can update because Oracle knows which views those are. Neat.
They call it Oracle for a reason.
Oracle Tips and Tricks -- David Fitzjarrell 