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) primary key,
ename varchar2(50) not null,
annual_leave number(4) default 0,
casual_leave number(4) default 0,
Lop number(4) default 0,
Year number(4) check (year between 2007 and 2008),
status varchar2(1) default 'N')


Wrong: insert into vacation values('2877','James',2007,'A');
-- Note: All column names ae not given

Output: ERROR at line 1: ORA-00947: not enough values

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