Using Views

Read Only Views
Check Option
View Constraints
FORCE

Views are simply stored query definitions. They can be used to encapsulate business logic and complex queries from clients, as well as offering a degree of security when used correctly.

There are quite a few options when utilising views, this will outline the most common and useful.

Read-Only Views

By default, if the view will allow it (via key-preservation), records in views can be subject to DML operations such as INSERT, UPDATE and DELETE. However, as a security issue, or good practice, you can prevent this by specifying the WITH READ ONLY option, i.e.

/* Firstly, create an updateable view */

SQL> CREATE VIEW v
  2  AS
  3  SELECT a FROM t;

View created.

SQL> UPDATE v SET a = 'Y';

1 row updated.

/* Now, create a READ ONLY view */

SQL> CREATE OR REPLACE VIEW v
  2  AS
  3  SELECT * FROM t
  4  WITH READ ONLY;

View created.

/* Attempting to update results in error (although, it's rather a silly error) */

SQL> UPDATE v SET a = 'Y';
UPDATE v SET a = 'Y'
             *
ERROR at line 1:
ORA-01733: virtual column not allowed here

Check Option

The WITH CHECK OPTION option guarantees that data cannot be DML'ed within the view that would result in the view not being able to subsequently select the data.

Note, this does not get enforced if using INSTEAD OF triggers.

SQL> CREATE OR REPLACE VIEW v
  2  AS
  3  SELECT * FROM t
  4  WHERE a = 'X'
  5  WITH CHECK OPTION;

View created.

SQL> SELECT * FROM v;

A
----------
X

SQL> UPDATE v SET a = 'Y';
UPDATE v SET a = 'Y'
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

View Constraints

You can specify UNIQUE, PRIMARY KEY and FOREIGN KEY constraints on views, but be aware that they are not enforced. You have to specify the RELY DISABLE NOVALIDATE on views, which ensures they do not get enforced.

They are there to enable the database designer to provide information (or "metadata").

This metadata can help client tools to understand the relationships between views, and it can also help the optimizer when doing query rewrites on materialized views, simply because you've provided declarative information about relationships and data within the view(s).

The constraints are placed in the definition of the view.

SQL> CREATE OR REPLACE VIEW v
  2  (
  3    a UNIQUE RELY DISABLE NOVALIDATE,
  4    CONSTRAINT a_pk PRIMARY KEY (a) RELY DISABLE NOVALIDATE,
  5    CONSTRAINT a_fk FOREIGN KEY (a) REFERENCES t(a) RELY DISABLE NOVALIDATE
  6  )
  7  AS
  8  SELECT a FROM t
  9  /

View created.

/* Trying to create a view without RELY DISABLE NOVALIDATE results in error */

SQL> CREATE OR REPLACE VIEW v
  2  (
  3    a UNIQUE RELY DISABLE NOVALIDATE,
  4    CONSTRAINT a_pk PRIMARY KEY (a),
  5    CONSTRAINT a_fk FOREIGN KEY (a) REFERENCES t(a)
  6  )
  7  AS
  8  SELECT a FROM t
  9  /
  CONSTRAINT a_pk PRIMARY KEY (a),
                  *
ERROR at line 4:
ORA-00922: missing or invalid option

*DEV NOTE*
Even though the constraint on a view is DISABLE(d), you will not be able to drop the views / tables referenced until the constraints are removed, i.e.

SQL> DROP TABLE t;
DROP TABLE t
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

FORCE

The FORCE option of the CREATE VIEW statement can be used to create the object even if one of the underlying objects (i.e. referenced within the view) do not exist.

This can be useful if the views are created before the underlying objects in creation scripts etc.

/* Try to create a view against a table which does not exist */

SQL> CREATE OR REPLACE VIEW test_view
  2  AS
  3  SELECT * FROM non_existent_table;
SELECT * FROM non_existent_table
              *
ERROR at line 3:
ORA-00942: table or view does not exist

/* Hence, the view does not exists */

SQL> SELECT * FROM test_view;
SELECT * FROM test_view
              *
ERROR at line 1:
ORA-00942: table or view does not exist

/* Specifying FORCE creates the view object (albeit with errors) */

SQL> CREATE OR REPLACE FORCE VIEW test_view
  2  AS
  3  SELECT * FROM non_existent_table;

Warning: View created with compilation errors.

/* Trying to SELECT from the view implies it's been created */

SQL> SELECT * FROM test_view;
SELECT * FROM test_view
              *
ERROR at line 1:
ORA-04063: view "ORAUSER.TEST_VIEW" has errors

/* Creating the missing object then allows us to select from it */

SQL> CREATE TABLE non_existent_table
  2  (
  3    a  VARCHAR2(10)
  4  );

Table created.

SQL> SELECT * FROM test_view;

no rows selected