Oracle Create View Syntax: 9 Different Examples


  1. Create Simple View with Selected columns of a table
SQL> create view vw_emp as
select empno, ename from emp;

View created.

  1. Create a View with all columns of a table
SQL> create view vw_emp as select * from emp;


View created.

  1. Create view with columns from multiple tables
SQL> create view vw_emp_dept as
select empno, ename, dname
from   emp, dept
where  emp.deptno=dept.deptno;

View created.

  1. Modify the View definition - create or replace
SQL> create or replace view vw_emp_dept as
select empno, ename, dname
from   emp, dept
where  emp.deptno=dept.deptno
and    sal>5000;

View created.

  1. Update view rows - 
It requires the update privilege on the underlying tables.
SQL> update vw_emp_dept set empno=9999;

14 rows updated.

  1. Non Updatable views – A view with distinct, group by, aggregate functions cannot be updated. Row manipulation is not possible for the underling tables for these tables.

create view vw_emp as select distinct ename from emp;

SQL> update vw_emp set ename ='xyz';
update vw_emp set ename ='xyz'
       *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

  1. Create View without base table using FORCE clause. The view will be created with compilation errors.
SQL> create force view VW_Force_Invalid
as select * from bonus;

Warning: View created with compilation errors.

SQL>  select text from user_errors where name='VW_FORCE_INVALID';

TEXT
---------------------------------------------------------------
ORA-00942: table or view does not exist


  1. To describe the structure of the view
SQL> desc vw_emp_dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 DNAME                                              VARCHAR2(14)

  1. To drop a view

SQL> drop view vw_emp;


View dropped.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google