Oracle SQL - Data Manipulation via Views

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

Introduction

Views are virtual tables.

A view is updatable if the DML command against the view can be converted to DML commands against rows and columns of the underlying base tables.

Consider the following view. 

create or replace view emp as
select empno, ename, init
from   emp;

The Oracle DBMS can delete rows from the EMPLOYEES table via this view.

And you can change any of the three column values exposed by the view.

Inserting new rows via this view is impossible, because the EMPLOYEES table has NOT NULL columns without a default value outside the scope of the EMP view. 

//Testing DML Commands Against the EMP View
delete from emp
where  empno = 7005;

update emp
set    ename = 'BLACK'
where  empno = 7006;

SQL> insert into emp
  2  values (7999,'NEWGUY','NN');
insert into e
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("BOOK"."EMPLOYEES"."BDATE")

Related Topics