What is a VIEW in Oracle? Complete Explanation with Diagrams: Structure, Use Cases, and Creation Method

Views (VIEW) are essential objects in Oracle Database that allow you to simplify complex queries, manage security, and present customized data sets. In this article, we will provide a full overview of how views work, their use cases, how to create them, and their limitations, all explained with text diagrams and examples.

What is a View?
Benefits of Using Views
Example of Creating a View
Updatable vs Non-updatable Views
1. Updatable View
2. Non-updatable View
View with WHERE Clause
How to Check the Definition of a View
Dropping a View
Summary

What is a View?

A view is a virtual table created based on the result of a SELECT query. Unlike physical tables, views do not store data themselves but return data dynamically from the underlying tables.

┌─────────────┐     ┌─────────────┐
│  　　　EMP Table　　　   │     │ 　　　 DEPT Table　　　　│
└──────┬──────┘     └──────┬──────┘
    　　　  　│   　　　　　　　                │
  　    　　　▼         　　　　　　　          ▼
   　　　　　　 SELECT ... JOIN ... FROM EMP, DEPT
              │
              ▼
         ┌─────────┐
         │ 　　 VIEW 　　　 │
         └─────────┘

Benefits of Using Views

Benefit	Description
Simplifies complex queries	Encapsulates complex JOINs and filters
Enhances security	Restricts access to specific columns or rows
Provides abstraction	Hides physical table structures
Enables consistent results	Centralizes business logic in one place

Example of Creating a View

CREATE VIEW emp_dept_v AS
SELECT e.empno, e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno;

You can now run:

SELECT * FROM emp_dept_v;

Updatable vs Non-updatable Views

Updatable View

A view is updatable when it is based on a single table and does not contain any of the following:

GROUP BY
DISTINCT
Aggregate functions (e.g., SUM, AVG)
Joins
Subqueries in SELECT

Example:

CREATE VIEW emp_simple_v AS
SELECT empno, ename FROM emp;

UPDATE emp_simple_v SET ename = 'JONES' WHERE empno = 7369;

Non-updatable View

If a view includes a JOIN or GROUP BY, it becomes non-updatable.

CREATE VIEW emp_dept_v AS
SELECT e.empno, e.ename, d.dname
FROM emp e JOIN dept d ON e.deptno = d.deptno;

-- The below will result in an error:
UPDATE emp_dept_v SET ename = 'JONES' WHERE empno = 7369;

Why? Because Oracle cannot determine which base table should be updated from a joined view.

View with WHERE Clause

You can add conditions directly inside the view definition:

CREATE VIEW sales_emp_v AS
SELECT * FROM emp WHERE job = 'SALESMAN';

How to Check the Definition of a View

SELECT text FROM user_views WHERE view_name = 'EMP_DEPT_V';

Dropping a View

DROP VIEW emp_dept_v;

Summary

A view is a virtual table created by a SELECT statement.
Useful for abstraction, security, and simplifying queries.
Not all views are updatable — especially those with JOINs or aggregations.
Use user_views to check definitions.

[reference]
CREATE VIEW