Databases Reference
In-Depth Information
In this example, the following error is thrown:
ORA-01402: view WITH CHECK OPTION where-clause violation
I've rarely seen the
WITH CHECK OPTION
used. Having said that, if your business requirements mandate that
updatable views only have the ability to update data selectable by the view query, then, by all means, use this feature.
Creating Read-Only Views
If you don't want a user to be able to perform
INSERT
,
UPDATE
, or
DELETE
operations on a view, then don't grant those
object privileges on the view to that user. Furthermore, you should also create a view with the
WITH READ ONLY
clause
for any views for which you don't want the underlying tables to be modified. The default behavior is that a view is
updatable (assuming the object privileges exist).
This example creates a view with the
WITH READ ONLY
clause:
create or replace view sales_rockies as
select sales_id, amnt, state
from sales
where state in ('CO','UT','WY','ID','AZ')
with read only;
Even if a user (including the owner) has privileges to delete, insert, or update the underlying table, if such an
operation is attempted, the following error is thrown:
ORA-42399: cannot perform a DML operation on a read-only view
If you use views for reporting, and never intend for the views to be used as a mechanism for modifying the
underlying table's data, then you should always create the views with the
WITH READ ONLY
clause. Doing so prevents
accidental modifications to the underlying tables through a view that was never intended to be used to modify data.
Updatable Join Views
If you have multiple tables defined in the
FROM
clause of the SQL query on which the view is based, it's still possible to
update the underlying tables. This is known as an updatable join view.
For reference purposes, here are the
CREATE TABLE
statements for the two tables used in the examples in this section:
create table emp(
emp_id number primary key
,emp_name varchar2(15)
,dept_id number);
--
create table dept(
dept_id number primary key
,dept_name varchar2(15),
constraint emp_dept_fk
foreign key(dept_id) references dept(dept_id));
