How to perform DML operations on Oracle View: Insert, Update, delete

DML operations like insert, update, and delete are allowed in Oracle views with certain restrictions. Let’s check in this post to update the view in Oracle, insert into view Oracle, delete from view in Oracle

DML on view in Oracle

• We can perform DML operations like insert, delete and update on the view just like the Oracle table also with some restrictions
• A VIEW in Oracle is created by joining one or more tables. When you update record(s) in a VIEW, it updates the records in the underlying tables that make up the View.
• But of course, you need the appropriate privileges for the underlying tables
• DML operations on views can be performed as long as the view does not contain these words: DISTINCT, GROUP BY, START WITH, CONNECT BY, ROWNUM, UNION [ALL], INTERSECT, MINUS, or view has subqueries.
• When using the WITH CHECK OPTION [CONSTRAINT <constraint_name>] clause, one can restrict the values inserted using the views:

For example,

Let’s first create the TEST view.

Create view emp_data  as select e.empno,e.ename,e.sal,e.deptno,d.dname From emp e, dept d where e.deptno=d.deptno;

To view allowable DML operations on a view, query the USER_UPDATABLE_COLUMNS view.

select table_name,column_name,updatable,insertable,deletable from USER_UPDATABLE_COLUMNS  where table_name='EMP_DATA'; 

Let’s try to insert

Similarly, we can delete from the view and update also based on USER_UPDATABLE_COLUMNS

View with check option in Oracle

The WITH CHECK OPTION clause is used with an updatable view to make sure that changes to the view don’t make rows that aren’t in the query.

Now take an example with the check option in Oracle

CREATE VIEW EMP_SAL AS SELECT EMPNO, ENAME ,SAL FROM EMP WHERE SAL < 2000 WITH CHECK OPTION; 

One can use DML operation on this view only on values smaller than 2000 only

-When specifying the constraint name, Oracle will create the constraint using the specified name (otherwise will create using SYS_C followed by a unique string).

Oracle will report an ORA-01402 error when the violation of the check option happens.

Related Articles

Sequences in Oracle: What is Sequence in Oracle, Description of all options, How to create them, Oracle 12c new feature of sequences, How to drop and alter them
Indexes in Oracle: Check out Oracle indexes information, different types of indexes in Oracle with an example, how to create/drop/alter the index in Oracle
Oracle Materialized Views: what is Oracle materialized view, Oracle Materialized View Log, Refresh Group, Difference between view and materialized view
How to monitor the progress of refresh of Materialized views
oracle alter table modify column type: Check out this post on how to modify columns in Oracle using the oracle alter table modify the column. Modify column can be changing default value, allowing not null
How to create view in Oracle: Complete guide on how to create the view in Oracle, drop Oracle views, alter Oracle views, How to determine the query of the already created view
Top-N Queries in Oracle: Check out this page to explore the various ways to achieve Top-N Queries in Oracle like oracle top 100, selecting top 10 rows in oracle
Auto Increment in Oracle: Check out how to use Auto Increment in Oracle before 12c version and with 12 and above version with detailed examples