modifiable vs non modifiable views by manish sharma

Modifiable and Non Modifiable views are an important and recurring concept in Oracle Database. Moreover, they have many practical applications and hence are necessary to know.

Modifiable And Non Modifiable Views In Oracle Database

A view is naturally modifiable if you do not require INSTEAD OF triggers to insert, delete or update data as well as if it complies to the restrictions discussed herewith. If the view query comprises of any of the mentioned constructs, then it is not naturally modifiable and therefore you cannot perform inserts, updates, or deletes on the view:

  • Set operators
  • Aggregate functions
  • GROUP BY, CONNECT BY or START WITH clauses
  • The DISTINCT operator
  • Joins (however, some join views are updatable)

In case a view consists of pseudo-columns or expressions, then it is only possible to update it with an UPDATE statement and that also when it does not refer to any such pseudo columns or for that matter, expressions.

I hope this short-note clears your concept. Also, make sure to follow me on my Facebook Page for more such interesting concepts. Thanks & have a great day!