Updating oracle views

Posted by / 01-Aug-2020 02:49

However, now you realize that you must redefine the ACCOUNTS_STAFF view to correct the department number specified in the WHERE clause of the defining query, because it should have been 30.

To preserve the grants of object privileges that you have made, you can replace the current version of the ACCOUNTS_STAFF view with the following statement: CREATE OR REPLACE VIEW Accounts_staff AS SELECT Empno, Ename, Deptno FROM Emp WHERE Deptno = 30 WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst; Replacing a view has the following effects: With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a view.

Views are very powerful and handy since they can be treated just like any other table but do not occupy the space of a table.

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.The query that defines the ACCOUNTS_STAFF view references only rows in department 10.Furthermore, WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATE statements issued against the view are not allowed to create or result in rows that the view cannot select.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.Instead of giving this join query again and again, we can create a view on these table by using a CREATE VIEW command given below create view emp_det as select e.empno, e.ename,e.sal,e.deptno,d.dname,from emp e, dept d where e.deptno=d.deptno; Now to see the employee details and department names we don’t have to give a join query, we can just type the following simple query.

updating oracle views-4updating oracle views-89updating oracle views-46

You can clearly see that I did not do such a good job with this. This SELECT can include a WHERE clause or anything else for that matter that can be put into a SELECT statement. This is a pretty common thing to do, By having a view that already does this we save having to write that function in every query where this is a requirement.

One thought on “updating oracle views”

  1. Petroleum / Oil Rig engineer - Denver / Poland Using the name Evan Gilmore, claiming to be 50 yr old divorced single father with son 14, daughter 17 living in Denver, grew up in Florida. James Edmund, DOB 12/9/1978, Dutch, Mechanical Engineer Oil Rig Miami Tries to groom you to start off with followed by trying to induce empathy because he cannot cash a cheque for his two weeks job on an oil rig as his work …

  2. Each case is always decided on the individual merits and the former service member would need to show he was at the contamination site, current medical evidence of a disability, and an opinion by a medical Doctor that his disability was as likely as not caused by the contamination. I was lucky it had not gotten into my lymph nodes and I did not have to have radiation or chemo or subsequent treatment.