Modifying SQL Views (Insert, Update and Delete on SQL Views)

Insert, Update and Delete SQL Views

You can use the "OR Replace" option. If the view exists it will be replaced with the new definition or a new view will be created. We can use Create or Replace option to create views instead of dropping the view and recreating it, as with this option the privileges granted on the view are preserved, but the dependent stored programs and view become invalid.

The view will become invalid whenever the base table is altered. We can recompile a view using the Alter view statement, but oracle automatically recompiles the view once it is accessed. On recompiling the dependent objects become invalid.

ALTER VIEW View-Name COMPILE ;

Use Drop View statement to drop a view.

SQL> drop view emp_info;<>View dropped.

Insert, Delete and Update on Views

When you update a view oracle will update the underlying base table. You can use DML operations on a view if the view does not include Group by, Start with, Connect by, Distinct clauses or any subqueries and set operations(Union, union all, intersect and minus).

Sql> create table t1(name varchar2(10), id number);
Table created.

Sql> select * from t1;
no rows selected

Sql> create view t1_view as select * from t1;
View created.

Sql> insert into t1_view values('a',1);
1 row created.

Sql> select * from t1;

NAME	ID
a	1

Sql> select * from t1_view;

NAME	ID
a	1

You can check whether the particular columns in a view are updateable or not:

For the t1_view created above we can check like this

Sql> select * from user_updatable_columns where table_name = 'T1_VIEW';

OWNER	TABLE_NAME	COLUMN_NAME	UPD	INS	DEL
SCOTT	T1_VIEW	NAME	YES	YES	YES
SCOTT	T1_VIEW	ID	YES	YES	YES

Another example of non updatable view

Sql> create view t1_secret as select name, id * 2 "secret id" from t1;
View created.
SQL> desc t1_secret;

Name	Null?	Type
NAME		VARCHAR2(10)
Secret ID		NUMBER

Sql> select * from user_updatable_columns where table_name = 'T1_SECRET';

OWNER	TABLE_NAME	COLUMN_NAME	UPD	INS	DEL
SCOTT	T1_SECRET	NAME	YES	YES	YES
SCOTT	T1_SECRET	SECRET ID	NO	NO	NO

WITH CHECK OPTION

Suppose we have a table t1

Sql> select * from t1;

NAME	ID
a	1
b	2
c	3
d	4
e	5

We will create a view t1_view on table t1:

Sql> create view t1_view as select name, id from t1 where id > 3;
View created.
Sql> desc t1_view;

Name	Null?	Type
NAME	NOT NULL	VARCHAR2(10)
ID		NUMBER

Sql> select * from t1_view;

NAME	ID
d	4
e	5

Now if we insert any row in the table through view with id less than or equal to 3 it will let us insert that.

Sql> insert into t1_view values('g',0);
1 row created.
Sql> select * from t1;

NAME	ID
a	1
b	2
c	3
d	4
e	5
g	0

6 rows selected.

Now we will use With Check option to create that view again:

Sql> create or replace view t1_view as select name, id from t1 where id > 3 with check option;
View created.

Now we will not be able to insert any row less than or equal to 3, only greater than 3 so as to match the view definition.

Sql> insert into t1_view values('k',1.5);
insert into t1_view values('k',1.5)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
Sql> insert into t1_view values('k',6);
1 row created