Consider the following simple view: CREATE VIEW Emp_view AS SELECT Ename, Empno, deptno FROM Emp; This view does not involve a join operation. In general, all modifiable columns of a join view must map to columns of a key-preserved table. Empno WITH CHECK OPTION; No deletion can be performed on this view because the view involves a self-join of the table that is key preserved.If you issue the SQL statement: UPDATE Emp_view SET Ename = 'SHAHRYAR' WHERE Empno = 109; then the EMP base table that underlies the view changes, and employee 109's name changes from ASHI to SHAHRYAR in the EMP table. If the view is defined using the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not modifiable. The following INSERT statement on the EMP_DEPT view succeeds, because only one key-preserved base table is being modified (EMP), and 40 is a valid DEPTNO in the DEPT table (thus satisfying the FOREIGN KEY integrity constraint on the EMP table).The following statement inserts a new row into the EMP table using the ACCOUNTS_STAFF view: INSERT INTO Accounts_staff VALUES (199, 'ABID', 30); Restrictions on DML operations for views use the following criteria in the order listed: The constraint created by WITH CHECK OPTION of the ACCOUNTS_STAFF view only allows rows that have a department number of 10 to be inserted into, or updated in, the EMP table.Alternatively, assume that the ACCOUNTS_STAFF view is defined by the following statement (that is, excluding the DEPTNO column): CREATE VIEW Accounts_staff AS SELECT Empno, Ename FROM Emp WHERE Deptno = 10 WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst; Considering this view definition, you can update the EMPNO or ENAME fields of existing records, but you cannot insert rows into the EMP table through the ACCOUNTS_STAFF view because the view does not let you alter the DEPTNO field. Loc IN ('HYD', 'BOM', 'DEL'); then there are restrictions on modifying either the EMP or the DEPT base table through this view.To alter the definition of a view, you must replace the view using one of the following methods: For example, assume that you create the ACCOUNTS_STAFF view, as given in a previous example.You also grant several object privileges to roles and other users.If you had defined a DEFAULT value of 10 on the DEPTNO field, then you could perform inserts. Loc FROM Emp e, Dept d /* JOIN operation */ WHERE e. A modifiable join view is a view that contains more than one table in the top-level FROM clause of the SELECT statement, and that does not contain any of the following: Any UPDATE, INSERT, or DELETE statement on a join view can modify only one underlying base table.
However, if you try to query the Oracle VIEW after the table has been dropped, you will receive a message indicating that the Oracle VIEW has errors.
select * from emp_det; This will show same result as you have type the long join query.
Now you can treat this EMP_DET view same as any other table.
Considering the example above, the following INSERT statement successfully inserts a row into the EMP table through the ACCOUNTS_STAFF view: INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10); However, the following INSERT statement is rolled back and returns an error because it attempts to insert a row for department number 30, which could not be selected using the ACCOUNTS_STAFF view: INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30); A view can be created even if the defining query of the view cannot be executed, as long as the CREATE VIEW command has no syntax errors. For example, if a view refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges, then the view can still be created and entered into the data dictionary.
You can only create a view with errors by using the FORCE option of the CREATE VIEW command: CREATE FORCE VIEW AS ...; When a view is created with errors, Oracle returns a message and leaves the status of the view as INVALID.