Version: 7.x - 11g and future version
Many junior DBA and developers always treat a database VIEW as table where they can simply UPDATE, but there are often they get ORA-01732 error, which I will explain the cause and solution
Example of error
SQL> UPDATE vwTestView set name='SCChen' where name='scchen';
UPDATE vwTestView set name='SCChen' where name='scchen'
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
Quick Verification for View Update
There are some restrictions but Oracle provide a system data dictionary which allows DBA and developers to quickly find out whether the column in the view can be used for update
The view name is as below:
1. USER_UPDATABLE_COLUMNS - filter by TABLE_NAME (which is view name)
2. ALL_UPDATABLE_COLUMNS - filter by OWNER, TABLE_NAME
3. DBA_UPDATABLE_COLUMNS - filter by OWNER, TABLE_NAME
Execute SQL below and supply the view's owner, and view name will allows you to see which column is updatable
select table_name, column_name, updatable from all_updatable_columns where table_name = &view_name and owner = &view_owner;
Explanation
A view often contains some translation, build-in function (sum, distinct, min), user defined function, combination of multiple columns (LastName + FirstName), or UNION from multiple tables. When they have these defined in the view, then the specific column, or even the whole view cannot be used for UPDATE.
Following are common examples that leads to this error
1. Build-in function DISTINCT is used, so it is impossible to update any column in the view
create view vwTestView as select distinct name from employee
2. TRIM, LTRIM, RTIM function is used, so that specific column cannot update
3. OLAP function is used. It is impossible to update any column in the view
create view vwTestView as
select a.*, first_value(sal) over() as Quarter_Salary
from employee a;
4. A GROUP BY is used
5. ORDER BY is used
6. Subquery (inner SELECT) is used
7. Keyword READ ONLY is used
8. MODEL is used
9. CONNECT BY is used
10. START WITH is used
11. UNION or UNION ALL is used
12. INTERSECT is used
13. MINUS is used
14. COUNT is used
15. MIN, MAX, AVG is used
16. SUM is used
17. SUBSTR is used
There is many reasons why Oracle (and many other vendors) restrict update on the VIEW. Following are some reasons
Let's take DISTINCT as an example. When a view contains DISTINCT, then it may display 3 rows of records for column GENDER (Male, Female, Null), even underlying contains 1 million records. If the database allowed user to update the view, then user may accidentally update 1 million records, instead of 3.
Another example... take following view which contains SUBSTR function, and create a virtual column
create view vwTestView as
select SUBSTR(Name_Mid, 1, 1) Name_Mid_Init, Name_First, Name_Last
from employee;
update vwTestView set Name_Mid_Init = 'K' where Name_Mid_Init = 'n';
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
Column Name_Mid_Init, which is the middle initial is derived from column Name_Mid. The view extract the 1st character from the column to pass to client application. This is commonly used by many applications. However, no database allows such modification as user must aware that it is taken from other column, and simply allow user to update this column will break the data consistency, and possibly break foreign key constraint, and check constraints that build on original column Name_Mid.
Some times I really tired of explaining this over and over again as many people just say the view has problem, please fix it, but they should study further the original intention of the view design, and they may not use the correct view, or they should design another views that suitable for the application instead
Kindly donate CAD$32 to appreciate my knowledge sharing, personal time, experience, and business value to your organization
No comments:
Post a Comment