Updating an Oracle view

Updating a view in Oracle means updating the base tables on which view is made. A view can be updatable or it can be non updatable. There are a few conditions that needs to be satisfied for a view to be updatable.

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.