Live sex bogans - Updating a view in oracle
Since you can't list more than one table in the Oracle UPDATE statement, you can use the Oracle EXISTS clause.The SQL UPDATE VIEW command can be used to modify the data of a view. You also grant several object privileges to roles and other users.
Now you can treat this EMP_DET view same as any other table.
For example, suppose all the employee working in Department No.
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.
If conditions later change so that the query of an invalid view can be executed, then the view can be recompiled and become valid.
An updatable view is one which allows performing a UPDATE command on itself without affecting any other table. The view is defined based on one and only one table. The view must include the PRIMARY KEY of the table based upon which the view has been created. The view should not have any field made out of aggregate functions. The view must not have any DISTINCT clause in its definition. The view must not have any GROUP BY or HAVING clause in its definition. The view must not have any SUBQUERIES in its definitions. If the view you want to update is based upon another view, the later should be updatable. Any of the selected output fields (of the view) must not use constants, strings or value expressions.
Whatever DML operations you performed on a view they actually affect the base table of the view. You can Query, Insert, Update and delete from views, just as any other table.The following sections explain how to create, replace, and drop views using SQL commands. To see the empno, ename, sal, deptno, department name and location we have to give a join query like this.select e.empno,e.ename,e.sal,e.deptno,d.dname,From emp e, dept d where e.deptno=d.deptno; So everytime we want to see emp details and department names where they are working we have to give a long join query.10 belongs to accounts department and most of the time you deal with these people.So every time you have to give a DML or Select statement you have to give a WHERE condition like ..... To avoid this, you can create a view as given below CREATE VIEW accounts_staff AS SELECT Empno, Ename, Deptno FROM Emp WHERE Deptno = 10 WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst; Now to see the account people you don’t have to give a query with where condition you can just type the following query.If you don’t want any DML operations to be performed on views, create them WITH READ ONLY option. When a user attempts to reference an invalid view, Oracle returns an error message to the user: ORA-04063: view 'view_name' has errors This error message is returned when a view exists but is unusable due to errors in its query (whether it had errors when originally created or it was created successfully but became unusable later because underlying objects were altered or dropped). For example: DROP VIEW Accounts_staff; Oracle allows you, with some restrictions, to modify views that involve joins. The following example shows an UPDATE statement that successfully modifies the EMP_DEPT_VIEW view: UPDATE Emp_dept_view SET Sal = Sal * 1.10 WHERE Deptno = 10; The following UPDATE statement would be disallowed on the EMP_DEPT_VIEW view: UPDATE Emp_dept_view SET Loc = 'BOM' WHERE Ename = 'SAMI'; This statement fails with an ORA-01779 error ("cannot modify a column which maps to a non key-preserved table"), because it attempts to modify the underlying DEPT table, and the DEPT table is not key preserved in the EMP_DEPT view.