Aim:

To create an updatable view and read only view on different tables.

PROCEDURE

* A view created using already existing table.

* If there is any updation or insertion is performed in existing table,the view table is

updated and inserted.

UPDATABLE VIEW

* We can view the table and make any manipulations on the view.

* Any change made to the view will be reflected both in the original table and the view.

Syntax:

Create view <new table name>(column1…..column n)as select (column1…..,column n) from <existing table name>; 

VIEW WITH READ ONLY

* We can only view the table, no alternation is performed.

* If we want to insert or update a record in case of view with read only,error message will be displayed.

Syntax:

Create view <new table name>(column1…..column n)as select (column1…..,column n) from <existing table name> with read only;

UPDATABLE VIEW EXAMPLE:

SQL> create table emp1(eno number(10),ename varchar(15),esal number,eadd varchar2(10))

Table created.

SQL> desc emp1;

Name                                      Null?    Type

————————————  ——– ————–

ENO                                                NUMBER(10)

ENAME                                           VARCHAR2(15)

ESAL                                               NUMBER(5)

EADD                                               VARCHAR2(10)

SQL> create view emp2 as select eno,ename,esal,eadd from emp1;

View created.

SQL> delete from emp1 where ename=’kannan’;

1 row deleted.

SQL> select * from emp1;

ENO   ENAME      ESAL      EADD

——    ——–        ——-      ——–

  1         arthi        2000       chennai

  3         raju         9000       madurai

  7         suda        8000      cuddalore

SQL> select * from emp2;

ENO  ENAME     ESAL     EADD

——  ———      ——-      ——-

1        arthi         2000      chennai

3        raju          9000      madurai

7        suda        8000      cuddalore

SQL> update emp2 set eadd=’karnataka’ where ename=’arthi’;

1 row updated.

SQL> select * from emp1;

ENO   ENAME     ESAL    EADD

——   ———-      ——     ——-

  1         arthi       2000     karnataka

  3         raju        9000     madurai

  7         suda      8000     cuddalore

SQL> select * from emp2;

ENO    ENAME      ESAL    EADD

——     ——–        ——      ——-

1           arthi         2000      karnataka

3           raju          9000      madurai

7           suda        8000      cuddalore

READ ONLY VIEW EXAMPLE:

SQL> create view empv1 as select eno,ename from emp1 with read only;

View created.

SQL> insert into empv1 values(2,’arthi’);

insert into empv1 values(2,’arthi’)

*

ERROR at line 1:

ORA-01733: virtual column not allowed here

SQL> create view empv2 as select eno,eadd from emp2;

View created.

SQL> select * from empv2;

ENO     EADD

——     ———

  1        karnataka

  3        madurai

  7        cuddalore

SQL> select * from emp2;

ENO      ENAME      ESAL        EADD

——      ———–     ——–      ———-

  1           arthi         2000        karnataka

  3           raju          9000        madurai

  7           suda        8000         cuddalore

SQL> select * from emp1;

ENO       ENAME     ESAL        EADD

——     ————     ——–      ———-

  1          arthi          2000        karnataka

  3          raju           9000        madurai

  7          suda         8000         cuddalore

SQL> insert into empv2 values(15,’arthg’);

1 row created.

SQL> select * from empv2;

ENO      EADD

——-    ———-

  1         karnataka

  3         madurai

  7         cuddalore

  15       arthg

SQL> create view empv3 as select eno from empv1 with read only;

View created.

SQL> select * from empv3;

ENO

——–

  1

  3

  7

  15

SQL> insert into empv3 values(34);

insert into empv3 values(34)

*

ERROR at line 1:

ORA-01733: virtual column not allowed here

RESULT

Thus the views were created for various tables and output was verified.