Creating Models › Target Servers for Physical Models › Database Property Editors › Oracle Physical Property Editors › Oracle Views Editor

Oracle Views Editor

In a physical model, you can create views, which are SQL queries that are permanently stored in the database under assigned names. The result of a view query is a virtual table. To the database user, a view appears just like a real table with a set of named columns and rows of data. However, a view is not a permanently stored set of data values. The rows and columns of data visible through the view are the result of the database query that defines the view.

You can use views in SQL to:

• Create a customized presentation of the data stored in a database.
• Restrict access to data, allowing different users access to only certain rows or columns of a table.
• Simplify database access by creating customized structures tailored to the needs of individual users. A user can write a simple query on a view rather than a complex query on the actual database tables.

Views are supported in the following features:

• Forward Engineering. When you forward-engineer your data model, the SQL code defining the view is generated to the database.
• Reverse Engineering. When you reverse-engineer an existing database that includes one or more views, each view is imported, the view syntax is parsed, and where possible, view relationships to the tables referenced by the view are created.
• Complete Compare. When you use complete compare and update a view either in the model or in the database, you can keep the model view specification in sync with the database view specification.

When you add a view to a model, the view graphically appears in the model as a box with a dotted outline and round corners, and a view relationship as a dotted line with circle at the view end of the line. A view relationship between a table, materialized view, or view and a view indicates that the view references one or more of the columns from that table, materialized view, or view.

If you delete a table column that is referenced by a view, you also deletes the corresponding view column. If you delete a table that is referenced by a view, you also delete the corresponding view columns.

Use the Oracle Views Editor to add and change the properties of a view.

You can open the Oracle Views Editor in one of the following ways:

• Select Database Views from the Model menu
• Right-click a view in the diagram, select Database View Properties from the menu, and select a property that you want to modify
• Right-click a view in the Model Explorer and select Properties
• Single-click a view in the Model Explorer and click the Property Editor icon

The Oracle Views Editor dialog includes the following tabs:

Select Add tables, views, and materialized views to a view column and create aliases and expressions.

From Specify the source tables, views, or materialized views.

Where Specify the search conditions and criteria to use when you generate the SQL code for a view.

User Defined SQL Change a user-provided DDL statement. This tab will be blank if no override has been provided.

SQL View the SQL code that is used during Forward Engineering.

Constraints Use for entering constraints for a view.

Synonym Create, edit, or delete synonyms that apply to the selected table.

Comment Add or edit comments for a view.

UDP Enter user-defined property values for a view.

The following describes all of the other options available in this dialog:

View

A drop-down list box that contains the views in the current model.

Name

To rename the view, enter a different name in the Name text box.

Owner

You can enter a name in this text box to assign a database Owner to the selected view.

Generate

Select this check box to generate a CREATE VIEW statement for this view in the schema generation script. If you do not select the check box, no DDL will be generated for this object during Forward Engineering.

Create or Replace

Select this check box if you want the view DDL that is generated during Forward Engineering to replace any occurrence of an identically-named view. If this check box is not selected, the view DDL generated will only attempt to create the object and will not attempt to replace it.

Force

DB Sync

Starts the Complete Compare task so you can synchronize the physical objects defined in your model with the information stored on the server.