Insert into views - Restrictions

You can also insert into a view, as long as the view does not contain one of the following:

• A DISTINCT operator

• A set operator (UNION, MINUS, and so on)

• An aggregate function (SUM,COUNT,AVG, and so on)

• A GROUP BY,ORDER BY, or CONNECT BY clause

• A subquery in the SELECT list

Note:
If violating the constraints, insert will also fail, for example:


scott@ORCL> create view emp_v3 as select ename, job from emp;

View created.

scott@ORCL> select * from emp_v3;

ENAME JOB
---------- ---------
Denis
SMITH CLERK
ALLEN SALESMAN
WARD SALESMAN
JONES MANAGER
MARTIN SALESMAN
BLAKE MANAGER
CLARK MANAGER
SCOTT ANALYST
KING PRESIDENT
TURNER SALESMAN
ADAMS CLERK
JAMES CLERK
FORD ANALYST
MILLER CLERK

15 rows selected.

scott@ORCL> insert into emp_v3 values('David', 'Clerk');
insert into emp_v3 values('David', 'Clerk')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO")