Oracle Views

Views with force option

Views can be created with error and later it can be compiled. For example creating a view on table which does not exist.

CREATE VIEW TEST_VIEW AS SELECT C1, C2 FROM TEST_TABLE;

As test_table does not exist, it will not create view and will give error also.

CREATE FORCE VIEW TEST_VIEW AS SELECT C1, C2 FROM TEST_TABLE;

This will create view with warning.

Later create test_table and compile the view. Now data can be selected from the view.

Read Only

CREATE VIEW ALL_LOCATIONS AS SELECT COUNTRY_ID, COUNTRY_NAME FROM LOCATIONS NATURAL JOIN COUNTRIES WITH READ ONLY;

WITH CHECK OPTION

CREATE OR REPLACE VIEW DEPT_ABOVE_250 AS

SELECT DEPT_ID DID, DEPT_NAME FROM DEPT WHERE DEPT_ID>250;

Now insert a row in view

INSERT INTO DEPT_ABOVE_250 VALUES(199, 'Temporary Dept');

This will insert in DEPT table.

SELECT * FORM DEPT WHERE DEPT_ID = 199;

this will give one row. Although the view is defined with a WHERE clause to verify DEPT_ID > 250, oracle did not enforce the condition when a new row was inserted. This can be inforced by using WITH CHECK OPTION clause.

CREATE OR REPLACE VIEW DEPT_ABOVE_250 AS

SELECT DEPT_ID DID, DEPT_NAME FROM DEPT WHERE DEPT_ID>250

WITH CHECK OPTION;

Now the insert statement for view will not work.

Join Views

View with more than one base table in the top-level from clause. An updatable join view can be used to update the base tables through the view.

A table in the join-view is key preserved, if the primary and unique keys of the table are unique in the view's resultset. A key-preserved table can be updated by the join view.

CREATE OR REPLACE VIEW COUNTRY_REGION AS

SELECT a.country_id, a.country_name, a.region_id, b.region_name from countries a, regions b

where a.region_id = b.region_id;

If the view is defined with the WITH CHECK OPTION, the columns joining the tables in join view cannot be updated.

Inline Views

Inline views are subqueries used in the FROM clause. These subqueries can have an ORDER BY clause.