Difference between view’s with WITH CHECK OPTION and WITH READ ONLY

Here we will learn the difference between views created with “WITH CHECK OPTION”  and “WITH READ ONLY“.

Applied to : Both applies to Views / Views based on other view.

Similarity : Used to restrict Insert and Update performed through the view.

Differences :

If view is created with “WITH READ ONLY” no data manipulation is allowed in any condition. Only selects are allowed against the views.

If view is created with “WITH CHECK OPTION” row insertion is allowed based on some condition. It is used for restricted DML operation.

Condition’s are like

Query shouldn’t contain

1. Group By Clause
2. Distinct Clause
3. Group Function
4. Refrences to any pseudo column rownum
5. References to any expression

Query can select created rows based on the where clause of create view statement.

Example

Table Created

Create table jbt_employee(name varchar2(20), address varchar2(20), phone number)

 

View Created with “WITH READ ONLY”

CREATE OR REPLACE FORCE VIEW jbt_read_only_view AS SELECT * FROM jbt_employee    
    WHERE phone < 8          
     WITH READ ONLY CONSTRAINT read_only_const_name

 

View Created with “WITH CHECK ONLY”

CREATE OR REPLACE FORCE VIEW jbt_check_option_view AS SELECT * FROM jbt_employee    
     WHERE phone < 8          
       WITH CHECK OPTION CONSTRAINT check_option_const_name

 

Error will be thrown for below queries in both the case.

insert into jbt_check_option_view values('name', 'address', 9);
Error Thrown : ORA-01402: view WITH CHECK OPTION where-clause violation

Note*: Query is trying to insert a row which view itself can not access.

 

insert into jbt_read_only_view values('name', 'address', 9);
Error Thrown : ORA-01733: virtual column not allowed here

Note*: Because DML operation is not allowed in any case.

 

After changing the query and providing the phone number < 8

insert into jbt_check_option_view values('name', 'address', 7);
Successfully Fired.

Now row will be created as it can be accessed by by view itself(Based on Where clause).

insert into jbt_read_only_view values('name', 'address', 7);
Error Thrown : ORA-01733: virtual column not allowed here

 

Pseudo Column

1. rowid
2. rownum
3. level
4. user
5. nextval   –  used in sequence(to fetch next value)
6. curval    –  used in sequence(to fetch current value)