For example, if only the WHERE clause of the view changes, dependent PL/SQL program units remain valid. In addition, dependent PL/SQL program units may become invalid, depending on what was changed in the new version of the view. If a constraint in the CHECK OPTION was previously defined but not included in the new view definition, the constraint is dropped.Īll views dependent on a replaced view become invalid (not usable). All underlying objects referenced by the view are not affected. Replacing a view replaces the view definition in the data dictionary. WITH CHECK OPTION CONSTRAINT sales_staff_cnst īefore replacing a view, consider the following effects: You can replace the current version of the sales_staff view with the following statement: However, now you must redefine the sales_staff view to change the department number specified in the WHERE clause. For example, assume that you created the sales_staff view as shown earlier, and, in addition, you granted several object privileges to roles and other users. The OR REPLACE clause replaces the current definition of a view and preserves the current security authorizations. You can redefine the view with a CREATE VIEW statement that contains the OR REPLACE clause. After the view is re-created, privileges must be regranted. When a view is dropped, all grants of corresponding object privileges are revoked from roles and users. WITH CHECK OPTION CONSTRAINT departments_hq_man_cnst (department_id, department_name, manager_id INVISIBLE, location_id)ĪS SELECT department_id, department_name, manager_id, location_id For example, the following statements creates the departments_hq_man view and makes the manager_id column invisible: You can also create views with invisible columns. If no WITH clause is specified, the view, with some restrictions, is inherently updatable. The view could have been constructed specifying the WITH READ ONLY clause, which prevents any updates, inserts, or deletes from being done to the base table through the view. INSERT INTO departments_hq VALUES (301, 'TRANSPORTATION', 2700) However, the following INSERT statement returns an error because it attempts to insert a row for location 2700, which cannot be selected using the departments_hq view: INSERT INTO departments_hq VALUES (300, 'NETWORKING', 1700) For example, the following INSERT statement successfully inserts a row into the departments table with the departments_hq view, which contains all rows with location 1700: Furthermore, the CHECK OPTION creates the view with the constraint (named departments_hq_cnst) so that INSERT and UPDATE statements issued against the view cannot result in rows that the view cannot select. The query that defines the departments_hq view references only rows in location 1700. WITH CHECK OPTION CONSTRAINT departments_hq_cnst SELECT department_id, department_name, location_id The following statement creates a view on a subset of data in the hr.departments table: As with all subqueries, the query that defines a view cannot contain the FOR UPDATE clause. Each view is defined by a query that references tables, materialized views, or other views. You can create views using the CREATE VIEW statement. If the owner of the view intends to grant access to the view to other users, the owner must have received the object privileges to the base objects with the GRANT OPTION or the system privileges with the ADMIN OPTION. For example, if the owner of the view has only the INSERT privilege for Scott's emp table, then the view can be used only to insert new rows into the emp table, not to SELECT, UPDATE, or DELETE rows. ![]() Also, the functionality of the view depends on the privileges of the view owner. The owner cannot have obtained these privileges through roles. The owner of the view (whether it is you or another user) must have been explicitly granted privileges to access all objects referenced in the view definition. You can acquire these privileges explicitly or through a role. To create a view in another user's schema, you must have the CREATE ANY VIEW system privilege. To create a view in your schema, you must have the CREATE VIEW privilege.
0 Comments
Leave a Reply. |