View conditions that allow DML operations

View conditions that allow DML operations

A view can shield information about some base tables, or join multiple base tables to form a complex query. A view itself can also perform DML operations, but is limited by some conditions.

Oracle DML Process

PL/SQL ORA-14551: cannot perform DML operations in queries solved

Common MySQL DDL, DML, and DCL languages (example)

Execute batch DML exercises for Oracle basic transactions and ForAll

Oracle DML Statement (insert, update, delete) rollback Estimation

First, let's take a look at the instructions on DML operations on views in the official documentation:

The following notes apply to updatable views:
 
An updatable view is one you can use to insert, update, or delete base table rows. you can create a view to be inherently updatable, or you can create an instead of trigger on any view to make it updatable.
 
The following describes two methods for updateable (including adding, deleting, and modifying base tables) views: one is to inherit the base table view, and the other is to use the INSTEAD trigger to implement updatable for any view.
 
To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. The information displayed by this view is meaningful only for inherently updatable views.
 
In the USER_UPDATABLE_COLUMNS data dictionary view, you can find the fields in the view that can be added, updated, and deleted.
 

For a view to be inherently updatable, the following conditions must be met:
 
For views inherited by updatable, the following conditions must be met:
 
1. each column in the view must map to a column of a single table. for example, if a view column maps to the output of a TABLE clause (an unnested collection), then the view is not inherently updatable.
 
2. The view must not contain any of the following constructs:
 
A set operator
 
A distinct operator
 
An aggregate or analytic function
 
A group by, order by, MODEL, connect by, or start with clause
 
A collection expression in a SELECT list
 
A subquery in a SELECT list
 
A subquery designated WITH READ ONLY
 
Joins, with some exceptions, as supported ented in Oracle Database Administrator's Guide
 
3. In addition, if an inherently updatable view contains pseudo docolumns or expressions, then you cannot update base table rows with an UPDATE statement that refers to any of these pseudo docolumns or expressions.
 
4. If you want a join view to be updatable, then all of the following conditions must be true:
 
If updatable is required for a join view, the following conditions must be met:
 
(1) The DML statement must affect only one table underlying the join.
 
DML must affect only one join table.
 
(2) For an INSERT statement, the view must not be created with check option, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.
 
The with check option statement cannot be used, and all columns to be inserted come from the key-preserved table.
 
The key-preserved table indicates that each primary key or unique key in the base table must also be unique in the join view.
 
(3) For an UPDATE statement, the view must not be created with check option, and all columns updated must be extracted from a key-preserved table.
 
UPDATE statement. The view cannot be created using with check option. The UPDATE field must also be from the key-preserved table.
 
5. for a DELETE statement, if the join results in more than one key-preserved table, then Oracle Database deletes from the first table named in the FROM clause, whether or not the view was created with check option.
 
DELETE statement. If the join result has multiple key-preserved tables, Oracle will only DELETE the records of the first table in the FROM clause, regardless of whether the view uses the with check option.

For more details, please continue to read the highlights on the next page:

1 2 Next Page