Oracle SQL Experts: Oracle Read-Only View FAQs with Examples

A read-only view is a view through which users can query data but cannot perform INSERT, UPDATE, or DELETE.

Oracle provides the WITH READ ONLY clause to explicitly make a view read-only.

1. What is a read-only view?

A read-only view allows:

SELECT

but prevents:

INSERT
UPDATE
DELETE

Example

CREATE OR REPLACE VIEW emp_report AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WITH READ ONLY;

Users can query:

SELECT *
FROM emp_report;

But this is not allowed:

UPDATE emp_report
SET salary = 60000
WHERE employee_id = 101;

2. Why do we use a read-only view?

Read-only views are commonly used for:

Reporting
Data security
Data presentation
Restricting DML
Providing controlled access to data
Exposing selected columns to users

For example:

CREATE OR REPLACE VIEW employee_report AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WITH READ ONLY;

The reporting user can read employee information without being able to modify it through the view.

3. What is the syntax for a read-only view?

CREATE [OR REPLACE] VIEW view_name AS
SELECT ...
FROM ...
[WHERE ...]
WITH READ ONLY;

Example:

CREATE OR REPLACE VIEW dept10_report AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WHERE department_id = 10
WITH READ ONLY;

4. Can we SELECT from a read-only view?

Yes.

SELECT *
FROM dept10_report;

A read-only view behaves normally for querying.

You can also use:

SELECT employee_id,
       first_name,
       salary
FROM dept10_report
WHERE salary > 50000;

The WITH READ ONLY clause does not prevent queries.

5. Can we INSERT into a read-only view?

No.

Example:

CREATE OR REPLACE VIEW dept10_report AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH READ ONLY;

Attempt:

INSERT INTO dept10_report
(
    employee_id,
    first_name,
    salary,
    department_id
)
VALUES
(
    201,
    'DAVID',
    60000,
    10
);

Oracle rejects the DML because the view is read-only.

You may encounter:

ORA-42399: cannot perform a DML operation on a read-only view

6. Can we UPDATE a read-only view?

No.

Example:

UPDATE dept10_report
SET salary = salary + 1000
WHERE employee_id = 101;

This fails because:

VIEW
  ↓
WITH READ ONLY
  ↓
UPDATE not allowed

7. Can we DELETE from a read-only view?

No.

Example:

DELETE FROM dept10_report
WHERE employee_id = 101;

This is rejected because the view is explicitly read-only.

8. Does WITH READ ONLY affect the underlying table?

No. This is an important point.

Suppose:

CREATE OR REPLACE VIEW emp_report AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WITH READ ONLY;

You cannot do:

UPDATE emp_report
SET salary = 70000
WHERE employee_id = 101;

But if the user has sufficient privileges on EMPLOYEES, this is still possible:

UPDATE employees
SET salary = 70000
WHERE employee_id = 101;

So:

WITH READ ONLY
      ↓
Restricts DML through the VIEW
      ↓
Does NOT make the underlying TABLE read-only

9. Is every complex view automatically read-only?

Not necessarily.

A complex view may contain:

Joins
Aggregates
GROUP BY
DISTINCT
Set operators
Calculated expressions

Many such views are not directly updatable, but you should not equate complex view with explicitly read-only view.

For example:

CREATE OR REPLACE VIEW dept_summary AS
SELECT department_id,
       COUNT(*) AS employee_count,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

This is normally not directly updatable because of the aggregation.

But:

WITH READ ONLY

explicitly declares that the view is read-only.

10. What is the difference between a non-updatable view and WITH READ ONLY?

This is an important interview question.

Non-updatable view

A view may naturally be non-updatable because of its definition.

Example:

CREATE OR REPLACE VIEW dept_summary AS
SELECT department_id,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

The GROUP BY makes normal direct DML inappropriate.

Explicit read-only view

CREATE OR REPLACE VIEW emp_report AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WITH READ ONLY;

This view might otherwise be updatable, but WITH READ ONLY explicitly prevents DML through it.

Remember:

Non-updatable view
      ↓
DML isn't supported because of view definition/rules

WITH READ ONLY
      ↓
DML is explicitly prohibited

11. Can a simple view be made read-only?

Yes.

Example:

CREATE OR REPLACE VIEW emp_view AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WITH READ ONLY;

Even though this is a simple view and could normally be updatable, WITH READ ONLY prevents DML.

12. Can a complex view be made read-only?

Yes.

Example:

CREATE OR REPLACE VIEW department_report AS
SELECT d.department_id,
       d.department_name,
       COUNT(e.employee_id) AS employee_count,
       AVG(e.salary) AS average_salary
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id,
         d.department_name
WITH READ ONLY;

This is a good reporting view.

Users can:

SELECT *
FROM department_report;

but cannot modify it.

13. Can a read-only view contain a WHERE clause?

Yes.

CREATE OR REPLACE VIEW high_salary_employees AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WHERE salary >= 100000
WITH READ ONLY;

The view returns only employees with:

salary >= 100000

And because of WITH READ ONLY, users cannot modify the data through the view.

14. Can a read-only view contain joins?

Yes.

Example:

CREATE OR REPLACE VIEW emp_dept_report AS
SELECT e.employee_id,
       e.first_name,
       e.salary,
       d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
WITH READ ONLY;

This is useful for reporting because users can see information from both tables without being able to modify either through the view.

15. Can a read-only view contain GROUP BY?

Yes.

CREATE OR REPLACE VIEW dept_salary_report AS
SELECT department_id,
       COUNT(*) AS employee_count,
       SUM(salary) AS total_salary,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
WITH READ ONLY;

Query:

SELECT *
FROM dept_salary_report;

Possible result:

DEPARTMENT_ID  EMPLOYEE_COUNT  TOTAL_SALARY  AVERAGE_SALARY
-------------  --------------  ------------  --------------
10             5               350000        70000
20             8               640000        80000
30             4               300000        75000

This is a classic read-only reporting view.

16. Can a read-only view contain aggregate functions?

Yes.

For example:

CREATE OR REPLACE VIEW salary_statistics AS
SELECT COUNT(*) AS employee_count,
       SUM(salary) AS total_salary,
       AVG(salary) AS average_salary,
       MIN(salary) AS minimum_salary,
       MAX(salary) AS maximum_salary
FROM employees
WITH READ ONLY;

This view is designed for reporting.

17. Can we use WITH READ ONLY with WITH CHECK OPTION?

No. They serve opposite purposes.

WITH READ ONLY
      ↓
DML prohibited

WITH CHECK OPTION
      ↓
DML may be allowed
but modified rows must satisfy
the view condition

For example:

CREATE OR REPLACE VIEW dept10_employees AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH CHECK OPTION;

This allows appropriate DML while enforcing the condition.

Whereas:

CREATE OR REPLACE VIEW dept10_report AS
SELECT employee_id,
       first_name,
       salary,
       department_id
FROM employees
WHERE department_id = 10
WITH READ ONLY;

allows querying but no DML.

Easy comparison

WITH CHECK OPTION
      ↓
DML allowed
      ↓
Don't violate WHERE condition

WITH READ ONLY
      ↓
DML not allowed

18. Can we use WITH READ ONLY with an INSTEAD OF trigger?

This is an important conceptual question.

An explicitly read-only view cannot be used as a normal DML interface simply by adding an INSTEAD OF trigger.

WITH READ ONLY declares that DML through the view is not allowed.

If you need DML through a complex view, an INSTEAD OF trigger is a common mechanism—but the view should not be explicitly declared WITH READ ONLY for that purpose.

19. Can a read-only view improve security?

Yes, but understand its scope.

Suppose the underlying table contains:

EMPLOYEE_ID
FIRST_NAME
SALARY
BANK_ACCOUNT
PASSWORD_HASH

You may expose only appropriate columns:

CREATE OR REPLACE VIEW employee_public_report AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WITH READ ONLY;

Then grant access to the view:

GRANT SELECT
ON employee_public_report
TO reporting_user;

The reporting user can query the view:

SELECT *
FROM employee_public_report;

but the view itself does not permit:

INSERT
UPDATE
DELETE

For security-sensitive systems, you should also carefully control direct privileges on the underlying tables.

20. Does WITH READ ONLY hide the underlying table?

No.

WITH READ ONLY does not automatically hide the underlying table.

For example:

CREATE OR REPLACE VIEW employee_report AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WITH READ ONLY;

The view does not automatically revoke:

SELECT

or DML privileges on:

EMPLOYEES

If a user already has direct privileges on EMPLOYEES, they may still access it directly.

21. Can we grant SELECT on a read-only view?

Yes.

Example:

GRANT SELECT
ON employee_report
TO reporting_user;

Now the user can execute:

SELECT *
FROM employee_report;

but the view itself does not permit:

INSERT
UPDATE
DELETE

22. Can a read-only view be used for reporting?

Yes. This is one of its most common uses.

Example:

CREATE OR REPLACE VIEW monthly_employee_report AS
SELECT department_id,
       COUNT(*) AS employee_count,
       SUM(salary) AS total_salary,
       AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
WITH READ ONLY;

Applications can simply use:

SELECT *
FROM monthly_employee_report;

without giving users a way to modify the report through the view.

23. Does a read-only view store data?

No.

A normal Oracle view stores the view definition, not a physical copy of the result.

Example:

CREATE OR REPLACE VIEW emp_report AS
SELECT employee_id,
       first_name,
       salary
FROM employees
WITH READ ONLY;

Conceptually:

EMPLOYEES
   ↓
VIEW DEFINITION
   ↓
EMP_REPORT
   ↓
SELECT
   ↓
Current data

If the underlying employee salary changes, the next query against the view reflects the current underlying data.

If you need physically stored query results, consider a materialized view.

24. Read-only view vs materialized view

Feature	Read-Only View	Materialized View
Stores query definition	✅	✅
Stores query result physically	❌	✅
SELECT	✅	✅
DML through view	❌	Different rules
Refresh required	❌	Often ✅
Good for reporting	✅	✅
WITH READ ONLY	✅	❌ Different concept

Example read-only view:

CREATE OR REPLACE VIEW dept_report AS
SELECT department_id,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
WITH READ ONLY;

Materialized view:

CREATE MATERIALIZED VIEW dept_report_mv
BUILD IMMEDIATE
REFRESH COMPLETE
ON DEMAND
AS
SELECT department_id,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

The materialized view stores the result physically and has refresh considerations.

25. How can I check whether a view is read-only?

You can inspect the data dictionary.

For example:

SELECT view_name,
       read_only
FROM user_views
WHERE view_name = 'EMP_REPORT';

The READ_ONLY metadata indicates whether the view was defined as read-only.

You can also inspect the complete view definition:

SELECT text
FROM user_views
WHERE view_name = 'EMP_REPORT';

26. What is a complete read-only reporting example?

Suppose we have:

CREATE TABLE departments (
    department_id   NUMBER PRIMARY KEY,
    department_name VARCHAR2(100)
);

and:

CREATE TABLE employees (
    employee_id   NUMBER PRIMARY KEY,
    first_name    VARCHAR2(100),
    salary        NUMBER,
    department_id NUMBER
);

Create a reporting view:

CREATE OR REPLACE VIEW department_report AS
SELECT d.department_id,
       d.department_name,
       COUNT(e.employee_id) AS employee_count,
       NVL(SUM(e.salary), 0) AS total_salary,
       NVL(AVG(e.salary), 0) AS average_salary
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id,
         d.department_name
WITH READ ONLY;

Query:

SELECT *
FROM department_report;

Possible result:

DEPARTMENT_ID  DEPARTMENT_NAME  EMPLOYEE_COUNT  TOTAL_SALARY  AVERAGE_SALARY
-------------  ---------------  --------------  ------------  --------------
10             IT               5               350000        70000
20             HR               3               210000        70000
30             SALES            8               640000        80000

Users can query the report but cannot execute:

UPDATE department_report ...

or:

DELETE FROM department_report ...

or:

INSERT INTO department_report ...

27. What are the most common read-only-view interview traps?

Trap 1

Can we SELECT from a read-only view?

✅ Yes.

Trap 2

Can we UPDATE a read-only view?

❌ No.

Trap 3

Can we INSERT into a read-only view?

❌ No.

Trap 4

Can we DELETE from a read-only view?

❌ No.

Trap 5

Does WITH READ ONLY make the underlying table read-only?

❌ No.

It only restricts DML through the view.

Trap 6

Does WITH READ ONLY store a copy of the data?

❌ No.

A normal view stores its definition, not its result.

Trap 7

Can a simple view be declared read-only?

✅ Yes.

Trap 8

Can a complex view be declared read-only?

✅ Yes.

This is very common for reporting views.

Trap 9

Does WITH READ ONLY mean the view is automatically secure?

❌ Not by itself.

You still need appropriate privileges on the view and underlying objects.

28. Read-Only View vs WITH CHECK OPTION

Feature	WITH READ ONLY	WITH CHECK OPTION
SELECT	✅	✅
INSERT	❌	Possible if otherwise updatable
UPDATE	❌	Possible if otherwise updatable
DELETE	❌	Generally possible if otherwise deletable
Enforces view WHERE condition	Not applicable to DML	✅
Makes view read-only	✅	❌
Makes non-updatable view updatable	❌	❌

Remember:

WITH READ ONLY
      ↓
NO DML THROUGH VIEW

WITH CHECK OPTION
      ↓
DML MAY BE ALLOWED
      ↓
BUT RESULT MUST SATISFY VIEW CONDITION

29. Read-Only View Cheat Sheet

READ-ONLY VIEW
      |
      ↓
WITH READ ONLY
      |
      +---------+---------+
      |         |         |
    SELECT    INSERT    UPDATE
      |         |         |
      ↓         ❌        ❌
     YES
      |
    DELETE
      |
      ❌

Key points to remember

Question	Answer
What is a read-only view?	A view that doesn't allow DML through the view
Syntax?	`WITH READ ONLY`
SELECT allowed?	✅
INSERT allowed?	❌
UPDATE allowed?	❌
DELETE allowed?	❌
Underlying table becomes read-only?	❌
Can simple views be read-only?	✅
Can complex views be read-only?	✅
Does it store data?	❌
Good for reporting?	✅
Same as WITH CHECK OPTION?	❌
Can it be queried normally?	✅

Most important interview statement

WITH READ ONLY explicitly prevents INSERT, UPDATE, and DELETE operations through a view while still allowing users to query the view.

The easiest distinction to remember is:

WITH READ ONLY
      ↓
No DML

WITH CHECK OPTION
      ↓
DML allowed when possible
      ↓
Don't violate the view's WHERE condition

Oracle SQL Experts

Home

Oracle Read-Only View FAQs with Examples

1. What is a read-only view?

2. Why do we use a read-only view?

3. What is the syntax for a read-only view?

4. Can we SELECT from a read-only view?

5. Can we INSERT into a read-only view?

6. Can we UPDATE a read-only view?

7. Can we DELETE from a read-only view?

8. Does WITH READ ONLY affect the underlying table?

9. Is every complex view automatically read-only?

10. What is the difference between a non-updatable view and WITH READ ONLY?

11. Can a simple view be made read-only?

12. Can a complex view be made read-only?

13. Can a read-only view contain a WHERE clause?

14. Can a read-only view contain joins?

15. Can a read-only view contain GROUP BY?

16. Can a read-only view contain aggregate functions?

17. Can we use WITH READ ONLY with WITH CHECK OPTION?

18. Can we use WITH READ ONLY with an INSTEAD OF trigger?

19. Can a read-only view improve security?

20. Does WITH READ ONLY hide the underlying table?

21. Can we grant SELECT on a read-only view?

22. Can a read-only view be used for reporting?

23. Does a read-only view store data?

24. Read-only view vs materialized view

25. How can I check whether a view is read-only?

26. What is a complete read-only reporting example?

27. What are the most common read-only-view interview traps?

28. Read-Only View vs WITH CHECK OPTION

29. Read-Only View Cheat Sheet

Most important interview statement

No comments:

Post a Comment