Updating oracle views

07 Dec

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.

Insert, Update and Delete SQL Views You can use the "OR Replace" option.

If the view exists it will be replaced with the new definition or a new view will be created.

If conditions later change so that the query of an invalid view can be executed, then the view can be recompiled and become valid.

updating oracle views-20updating oracle views-82updating oracle views-64updating oracle views-56

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.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).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.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.