You can create Views with all columns or selected columns from base table. If column names are not specified, View inherits the same columns from the base table.
--Creating view with all columns from base table CREATE OR REPLACE VIEW vw_dept AS SELECT * FROM Dept; View created. --Accessing view data SELECT * FROM vw_dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
View being a schema object is stored within the database either in valid state or in invalid state. View can operate in any SQL query only when it is in valid state. The invalid state view can’t be referred in operational logic, but stay in the database as forced views. As soon as the basic requirements of this view are fulfilled, the view automatically goes into valid state.
SELECT * FROM Dept_force; SELECT * FROM Dept_force * ERROR at line 1: ORA-00942: table or view does not exist --Force view creation when base table doesn't exist CREATE OR REPLACE FORCE VIEW vw_dept_force AS SELECT * FROM Dept_force; Warning: View created with compilation errors. --Accessing force view SELECT * FROM vw_dept_force; SELECT * FROM vw_dept_force * ERROR at line 1: ORA-04063: view "SCOTT.VW_DEPT_FORCE" has errors
If columns are declared with double quotes during table or view creation, then column names become case sensitive. In that case you have to follow the exact casing that is used during column declaration in the CREATE statement else it produces error.
--Error! Double quotes preserves the case of the identifier SELECT "Employee id", Name, Designation FROM Employees WHERE "Base Salary" > 1500; SELECT "Employee id", Name, Designation * ERROR at line 1: ORA-00904: "Employee id": invalid identifier --Using proper case using double quotes SELECT "Employee ID", Name, Designation FROM Employees WHERE "Base Salary" > 1500;