SAP Note 15033 - INSERT/UPDATE/DELETE using views

Symptom:

ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert
ORA-01732: data manipulation operation not legal on this view
ORA-01733: virtual column not allowed here (-> 1 through 9; 12)
ORA-00942: table or view does not exist (-> 10 through 12)
ORA-01402: view WITH CHECK OPTION where-clause violation
(-> 8 and 13)

Cause and prerequisites

Conditions for changing table contents using views

Solution

In addition to the elements not allowed for view queries
(ORDER BY, FOR UPDATE, and sequence pseudocolumns CURRVAL and NEXTVAL),
a view query may not contain the following elements if you want to
enable INSERT, UPDATE, or DELETE (in addition to SELECT) for that view:

Illegal elements:
=================

1. Group functions

(AVG, COUNT, GLB, LUB, MAX, MIN, STDDEV, SUM, VARIANCE)

2. Set operators
(UNION [ALL], INTERSECT, MINUS)

3. The clauses GROUP BY, CONNECT BY

4. The operator DISTINCT

5. Joins (joint selection from several tables or views)
(it may be possible to update some specific joins)

6. Pseudocolumns ROWID, ROWNUM, LEVEL as well as constants and grouped expressions
(restricted updates are possible: the update statement must not refer to these pseudocolumns, constants or expressions)

Further restrictions for individual functions:
============================================

7. INSERT is not possible when the Basis table of the view contains NOT-NULL columns that are not a component of the view and for which no defaults exist.

8. If the view contains a CHECK-OPTION (type V constraint) then INSERT and UPDATE are impossible for values that the view cannot itself display itself because of a possible WHERE condition. If a WHERE condition's view with CHECK-OPTION contains a subquery to the same basis table, INSERT and UPDATE are also not always possible if the row to be added or changed can be displayed by View.

(The CHECK OPTION check the status before INSERT/UPDATE!!!)
Example:
CREATE TABLE T1 (S1 NUMBER, S2 NUMBER);
CREATE VIEW V1 AS SELECT * FROM T1 WHERE S1 IN (SELECT S2 FROM T1);
INSERT INTO V1 VALUES (1,1); -> Error ORA-01402
but:
INSERT INTO T1 VALUES (4,1);
INSERT INTO V1 VALUES (1,1); -> "1 row created"!
SELECT * FROM V1; -> Line (1,1) is displayed

9. If the view query contains grouped expressions or constants (defined with alias!), then DELETE is always possible; INSERT and UPDATE, however, are only possible when none of these expressions or constants appear in the UPDATE-SET statement or in the INSERT statement. (The WHERE condition of a DELETE or UPDATE statement for a view may contain these grouped expressions and constants.)

General conditions (privileges, for ORA-00942):
===============================================

10. The owner of the view must also be the owner of the Basis table,
OR he/she or PUBLIC must have the object privileges
SELECT, INSERT, UPDATE, or DELETE for the Basis table or their
synonyms
OR he/she or PUBLIC must have the system privilege
ANY TABLE

11. The user of the view must also be owner of the view
OR he/she or PUBLIC must have the object priviliges
SELECT, INSERT, UPDATE, or DELETE for the view
OR he/she or public must have the respective system priviledge
ANY TABLE

OTHER:

12. What is described in nos. 1-11 is also true of Views placed between the basis table and views used by the user ("View on View on Table").

13. In views with subquery INSERT, UPDATE and DELETE are generally possible if the other conditions (see above, no. 8) have been fulfilled.