Oracle SQL - Non updatable Views

  1. HOME
  2. Oracle SQL
  3. View
  4. Data Manipulation via Views

Introduction

If you create a view with the WITH READ ONLY option, data manipulation via that view is impossible by definition.

Consider the following code 

create or replace view avg_evaluations as
select course
,      avg(evaluation) as avg_eval
from   registrations
group  by course;

The AVG_EVALUATIONS view definition contains a GROUP BY clause.

There would be no one-to-one map between the rows of the view and the rows of the underlying table.

Therefore, data manipulation via the AVG_EVALUATIONS view is impossible.

If you use SELECT DISTINCT in your view definition, it makes your view not updatable.

The set operators UNION, MINUS, and INTERSECT result in non-updatable views.

Try to avoid using SELECT DISTINCT in view definitions, each view access will force a sort whether or not you need it.

Related Topic