Oracle SQL - Updatable Join Views

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

Introduction

Consider the following code 

create or replace view crs_offerings as
select o.course as course_code, c.description, o.begindate
from   offerings o
       join
       courses   c
       on (o.course = c.code);


The CRS_OFFERINGS view is based on a join of two tables: OFFERINGS and COURSES.

You can perform some data manipulation via this view, as long as the data manipulation can be translated into corresponding actions against the two underlying base tables.

CRS_OFFERINGS is an example of an updatable join view.

The following code demonstrates testing some DML commands against this view. 

delete from crs_offerings where course_code = 'ERM';

insert into crs_offerings (course_code, begindate)
values ('JSON' , trunc(sysdate));

You may get the following error when update an updatable join view:

  • ORA-01732: data manipulation operation not legal on this view
  • ORA-01752: cannot delete from view without exactly one key-preserved table
  • ORA-01779: cannot modify a column which maps to a non key-preserved table

Related Topic