Restrictions on updating a view in Oracle:
- A view having following clause cannot be updated in Oracle:
- A set operator
- A DISTINCT operator
- An aggregate or analytic function
- A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
- A collection expression in a SELECT list
- A sub query in a SELECT list
- A sub query designated WITH READ ONLY
Example,
Consider a scenario.
There are three tables: emp_details, emp_wages, department as follows:
Consider a view v_emp --create or replace view v_emp as select emp_name name,emp_location location,dept_name department,
basic_pay basic,hra hra,pf pf
from emp_details detl, emp_wages wages, department dept
where
detl.emp_id= wages.emp_id
and wages.dept_id= dept.dept_id;
The given view is updatable as it does not contain any of the restictive clause in its definition.
Now, consider a view v_emp_distinct --
create or replace view v_emp_distinct as select distinct emp_name name,emp_location location,dept_name department,
basic_pay basic,hra hra,pf pf
from emp_details detl, emp_wages wages, department dept
where
detl.emp_id= wages.emp_id
and wages.dept_id= dept.dept_id;
As the view contains distinct clause, so the view is not updatable.
Again consider a view v_emp_aggr --
create or replace view v_emp_aggr as select emp_location location,
sum(basic_pay) Total_basic
from emp_details detl, emp_wages wages, department dept
where
detl.emp_id= wages.emp_id
and wages.dept_id= dept.dept_id
group by emp_location;
The clause in bold letters indicates that the views are not updatable.
You can yourself copy paste the codes to verify the restrictions.
