Oracle SQL Views: CREATE, Types and DML Rules

Oracle SQL Views: Complete Guide

A view is a stored SELECT statement that you can query like a table. It provides a convenient way to simplify complex queries, hide sensitive columns, and control data access without storing any data itself.

Learning Objectives
By the end of this guide you will know how to create views, understand the difference between simple and complex views, learn when DML is allowed through a view, and use WITH CHECK OPTION and WITH READ ONLY correctly.

1. What a View Is

A view does not store data. It is simply a saved query. Every time you query the view, Oracle runs the underlying SELECT statement against the base tables and returns the current results.

CREATE VIEW emp_dept50
AS
SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  department_id = 50;

Important: Views are dynamic. Changes in the base tables are immediately visible the next time you query the view.

2. Creating Views

-- Basic view
CREATE VIEW emp_dept50
AS
SELECT employee_id, last_name, salary
FROM   employees
WHERE  department_id = 50;

-- Using column aliases in the header
CREATE VIEW emp_summary (id, name, annual_sal)
AS
SELECT employee_id, last_name, salary * 12
FROM   employees;

-- Modify an existing view
CREATE OR REPLACE VIEW emp_dept50
AS
SELECT employee_id, last_name, salary, job_id, department_id
FROM   employees
WHERE  department_id = 50;

Note: Any expression in the SELECT list (such as salary * 12) must have a column alias.

3. Simple Views vs Complex Views

Feature	Simple View	Complex View
Number of tables	One table	Multiple tables or JOINs
GROUP BY / aggregates	No	Yes
DISTINCT	No	Yes
DML allowed?	Usually yes	Usually no

4. DML Through Views

DML (INSERT, UPDATE, DELETE) is allowed only through simple views in most cases.

Restrictions: - Simple single-table views without GROUP BY, DISTINCT, or aggregates generally allow DML. - Views containing JOINs, GROUP BY, DISTINCT, or expressions usually do not allow INSERT or UPDATE on those columns.

5. WITH CHECK OPTION

This option prevents INSERT or UPDATE operations that would make a row invisible through the view.

CREATE VIEW emp_dept50
AS
SELECT employee_id, last_name, salary, department_id
FROM   employees
WHERE  department_id = 50
WITH CHECK OPTION;

-- This UPDATE is rejected
UPDATE emp_dept50
SET    department_id = 80
WHERE  employee_id = 121;

6. WITH READ ONLY

This makes the view completely read-only.

CREATE VIEW emp_readonly
AS
SELECT employee_id, last_name, salary
FROM   employees
WITH READ ONLY;

7. Modifying and Dropping Views

-- Replace existing view
CREATE OR REPLACE VIEW emp_dept50 ... ;

-- Drop a view
DROP VIEW emp_dept50;

DROP VIEW removes only the view definition. The base tables and their data are unaffected. However, dropping a base table makes any dependent views invalid.

8. Interactive Practice Quiz

Q1. A view is created with WITH CHECK OPTION and WHERE department_id = 50. What happens if you try to change a row’s department_id to 80?

A. The update succeeds B. The update is rejected by WITH CHECK OPTION C. The row is updated and disappears from the view D. The update succeeds only on the department_id column

Q2. Which view generally allows INSERT, UPDATE, and DELETE?

A. A view that joins two tables B. A simple single-table view without GROUP BY or aggregates C. A view using DISTINCT or aggregate functions D. A view containing salary * 12

Q3. What happens if you drop the base table a view depends on?

A. The view is automatically dropped B. The view remains but becomes invalid C. The view continues to work with old data D. Oracle prevents dropping the table

Conclusion

Views are a practical way to simplify queries and control data access. Understanding the difference between simple and complex views, along with the rules for DML and the WITH CHECK OPTION and WITH READ ONLY clauses, helps you use them effectively and safely.

Practice creating both types of views and testing DML behavior to get comfortable with how they work in real scenarios.