RSS Feed

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;

NAMEID
a1


Sql> select * from t1_view;

NAMEID
a1


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';

OWNERTABLE_NAMECOLUMN_NAMEUPDINSDEL
SCOTTT1_VIEWNAMEYESYESYES
SCOTTT1_VIEWIDYESYESYES


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;

NameNull?Type
NAME
VARCHAR2(10)
Secret ID
NUMBER


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

OWNERTABLE_NAMECOLUMN_NAMEUPDINSDEL
SCOTTT1_SECRETNAMEYESYESYES
SCOTTT1_SECRETSECRET IDNONONO


WITH CHECK OPTION

Suppose we have a table t1

Sql> select * from t1;

NAMEID
a1
b2
c3
d4
e5


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;

NameNull?Type
NAMENOT NULLVARCHAR2(10)
ID
NUMBER


Sql> select * from t1_view;

NAMEID
d4
e5


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;

NAMEID
a1
b2
c3
d4
e5
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