Oracle Tips and Tricks — David Fitzjarrell

April 23, 2009

You Can’t Do That … Or, Can You?

Filed under: General — dfitzjarrell @ 15:21

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.

Create a free website or blog at WordPress.com.