7 ViewsHow do I…
7.2 Determine all of the views owned by a particular user?
7.3 Determine if a view can be updated?
7.4 Create an updatable join view?
7.5 Simulate a cross-tab query using a view?
7.6 Re-create CREATE VIEW statements?
7.7 Implement record level security using views?
A view is a logical table based on a query. Views themselves do not store data, but present data from other sources, such as tables and other views. To an application, a view looks and acts like a table. Data can be queried from a view, and in many cases, data can be inserted, updated, and deleted through a view. Views can be used to present data in a different format than how it is actually stored in the underlying tables. Views simplify the presentation of data by hiding the actual structure of the base tables, and can secure data at the row level by presenting a restricted subset of the underlying table to the user.
7.1 Create a View
A view can be created for several reasons. Views can be used to represent queries on tables or other views in a virtual table. Views are used mostly to make a subset of data, or to join several tables, all invisible to the user accounts. This is useful for increased data security and simplifying code. This How-To shows the method for creating views.
7.2 Determine All of the Views Owned by a Particular User
The views available to a user can be queried from the data dictionary through the USER_VIEWS and ALL_VIEWS data dictionary views. Because it is possible to have many views available, an easy method to determine the views available is presented in this How-To.
7.3 Determine if a View Can Be Updated
A view’s data can be manipulated if the structure of the view meets certain criteria. A view cannot be updated if it joins tables, uses a DISTINCT operator, or contains a GROUP BY clause or group functions. This How-To presents SQL and PL/SQL statements to quickly determine if a view is updatable, without analyzing the structure of the view.
7.4 Create an Updatable Join View
Having the ability to update a view based upon more than one table allows for great flexibility in developing applications. If a table in a join view is key-preserved, it may be updated, depending on additional criteria. The ability to update object views are also available. This How-To describes the conditions that join views may be updatable, and steps through creating an updatable join view.
7.5 Simulate a Cross-Tab Query Using a View
A cross-tab query in Microsoft Access presents data from multiple rows on a single line. This type of query is useful for presenting data, such as monthly summary data, in a spreadsheet type format. This How-To presents a method to create a view that displays multiple records as a single record.