View in Oracle is a logical table and a physical schema object.

Why View is called as logical table and physical object?

Ans: View is,

• Physical object as it is stored within the database.
• Logical table as, view contains no data by itself. View contains only the SELECT statement. Whenever you write a query which includes View, the base query is executed to get the data for you.

Use the CREATE OR REPLACE VIEW statement to define a view. You can create a View-

• From one table/view
• Form multiple tables/views through joins or set operations
• From combination of tables and views.

The tables upon which a view is based are called base tables.

Syntax:

CREATE [OR REPLACE] [FORCE] VIEW <View Name> [Column list]
AS
<Select Query>

Keyword OR REPLACE and FORCE are optional with the CREATE statement. 

• OR REPLACE is used to replace an existing view having same name. If you do not use OR REPLACE while creating a view and if view exists in database then Oracle throws object already exists error.
• FORCE is used in situations where you want to create a view regardless of following-
  • The base tables of the view does not exist
  • The referenced object types does not exist
  • The owner of the schema containing the view does not have required privileges on the base tables or on referenced objects.

Let us create some views. You can create views by specifying your column names or can leave the column names as is in base table.

--Creating view with same name as in base table
CREATE VIEW vw_employees
AS 
SELECT
    Empno,
    Ename,
    Sal,
    Job
FROM Emp;

View created.


--Verify the structure
DESC vw_employees

 Name                   Null?    Type
 ---------------------- -------- -------------------
 EMPNO                  NOT NULL NUMBER(2)
 ENAME                           VARCHAR2(10)
 SAL                             NUMBER(7,2)
 JOB                             VARCHAR2(9)

--Creating view with user specified names
CREATE VIEW vw_department(DeptID, Department, Location)
AS 
SELECT
    Deptno,
    Dname,
    Loc
FROM Dept;

View created.


--Verify the structure
DESC vw_department

 Name                   Null?    Type
 ---------------------- -------- -------------------
 DEPTID                 NOT NULL NUMBER(2)
 DEPARTMENT                      VARCHAR2(14)
 LOCATION                        VARCHAR2(13)

--Error! Not using OR REPLACE for an existing view
CREATE VIEW vw_employees
AS 
SELECT
    Empno "Employee ID",
    Ename Name,
    Sal   "Base Salary",
    Job   Designation
FROM Emp;

ERROR at line 1:
ORA-00955: name is already used by an existing object

--Using OR REPLACE with View definition
CREATE OR REPLACE VIEW vw_employees
AS 
SELECT
    Empno "Employee ID",
    Ename Name,
    Sal   "Base Salary",
    Job   Designation
FROM Emp;

View created.


--Check View Structure
DESC vw_employees;
 Name                   Null?    Type
 ---------------------- -------- -----------------
 ID NUMBER              NOT NULL NUMBER(4)
 NAME                            VARCHAR2(10)
 Basic Salary                    NUMBER(7,2)
 DESIGNATION                     VARCHAR2(9)


SELECT * FROM vw_employees;

 ID NUMBER NAME       Basic Salary DESIGNATION
---------- ---------- ------------ -----------
      7369 SMITH               800 CLERK
      7499 ALLEN              1600 SALESMAN
      7521 WARD               1250 SALESMAN
      7566 JONES              2975 MANAGER
      7654 MARTIN             1250 SALESMAN
      7698 BLAKE              2850 MANAGER
      7782 CLARK              2450 MANAGER
      7788 SCOTT              3000 ANALYST
      7839 KING               5000 PRESIDENT
      7844 TURNER             1500 SALESMAN
      7876 ADAMS              1100 CLERK
      7900 JAMES               950 CLERK
      7902 FORD               3000 ANALYST
      7934 MILLER             1300 CLERK

14 rows selected.

Oracle catalog to check View

Every object created by your Oracle database user is registered in USER_OBJECTS metadata.

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS 
  FROM USER_OBJECTS WHERE OBJECT_TYPE = 'VIEW';

OBJECT_NAME                    OBJECT_TYPE             STATUS
------------------------------ ----------------------- -------
VW_EMPLOYEES                   VIEW                    VALID
VW_DEPARTMENT                  VIEW                    VALID

Oracle provides TAB metadata that stores only tables and views.

SELECT * FROM TAB WHERE TABTYPE = 'VIEW';

TNAME                        TABTYPE   CLUSTERID
------------------------     --------- --------------------
VW_EMPLOYEES                 VIEW
VW_DEPARTMENT                VIEW

But all these metadata do not give the underlying structure or the select query that the view contains. Oracle provides USER_VIEWS that stores the query used during view creation.

SELECT VIEW_NAME, TEXT FROM USER_VIEWS;

VIEW_NAME                   TEXT                       
--------------------------- ---------------------------
VW_DEPARTMENT               SELECT
                            Deptno,
                            Dname,
                            Loc
                            FROM Dept

VW_EMPLOYEES               SELECT
                           Empno "Employee ID",
                           Ename Name,
                           Sal     "Base Salary",
                           Job

What happens when you query the view?

View does not contain data by itself. It gets the data from the base table(s). When you write SELECT on a view, since view is a logical table, Oracle first has to identify the query is on a view, not on a table. After identification, Oracle gets the select statement and executes to fetch data from base tables. For example, when you write SELECT * FROM VW_DEPARTMENT, Oracle does following operation.

• Identifies whether VW_DEPARTMENT is a view or a table from TAB metadata.
• In case if the object is a View, gets the SELECT statement from USER_VIEWS and executes to fetch rows
• If the object is a table, directly queries to get the rows as table contains data

Let us create some more views and learn more uses of views in our next article.