Oracle 10g Views

Oracle Concepts

Selvaraj V Anna University Chennai. Chennai – 25.

Oracle Views

1)

Can we define an index on a view?

An index cannot be defined on a view. Because view is virtual table, which consists of a subset of columns from one more tables. And to be precise, view is just a query saved in the Metadata. We cannot index a query. Restrictions imposed on views: 1. A view can be created only in the current database. 2. A view can be created only if there is a SELECT permission on its base

table

3. A Trigger or an Index cannot be defined on a view. 4. A view cannot derive its data from temporary tables. 5. The CREATE VIEW statement cannot be combined with other SQL statements in a single batch. 2) How can we create a view on a non existing table Use the keyword force with the create command. Eg:

create force view v222 as select * from emp111;

-- Note : view

name should not be existing 3) Even if default values are there, to use "insert into table values" option, once should provide all column names to be inserted. Eg> table: Vacation (empid varchar2(7) ename varchar2(50) annual_leave number(4) casual_leave number(4) Lop number(4) Year number(4) status varchar2(1) Wrong:

Output:

primary key, not null, default 0, default 0, default 0, check (year between 2007 and 2008), default 'N')

insert into vacation values('2877','James',2007,'A'); -- Note: All column names are not given ERROR at line 1: ORA-00947: not enough values Workaround:

Correct : insert into vacation (empid,ename,Year,status) values('267834','JOse Mathew',2007,'A');

4) How can you do multiple column updates using Update Command: Eg:

update vacation set ANNUAL_LEAVE=18, CASUAL_LEAVE=7; update vacation set (ANNUAL_LEAVE,CASUAL_LEAVE)=(select 18,7 from

dual); 5) Can you insert into, or update a view? Yes or No could be the answer. It varies on case to case. Cases whos below explains, when and where it is possible to insert into or update a view. Note: View doesnt contain any data. Only the underlying tables gets inserted or updated. Case1. View is created from a single table and is not created Read Only. Yes. This kind of views could be updated. The changes are effected in the underlying table. Case2: View is created from mutliple tables using join conditions Updateable IFF (only if both of the following conditions are satisfied)

a) a)

Tables should have proper Primay key foreign key relationships AND b) b) All columns used in the views should unambigously point to the columns of the respective tables which is being updated through the view. Case3: View created with READ ONLY option cannot be updated. Case4: All other views which doesnot satisfy Case1 and Case2 couldnot be updated. 6) Can you create a view on a view? Yes. We can. Even view on combination of views is also possible. Eg:

create view v2 as select * from v1