Edition-based redefinition EBR brought two new schema objects: Editions Editioning views Editions extend the namespace for an object. The default for every database. One in each edition. Improve this answer. Chris Saxon Chris Saxon 2, 17 17 silver badges 16 16 bronze badges.
Assuming you are on Oracle 11g release 2: Your editioning view name and table name should not be the same. Mark Stewart Mark Stewart 1, 1 1 gold badge 10 10 silver badges 28 28 bronze badges. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. You use these to maintain columns present in one edition but not another. They define whether each edition can have its own version of a view it's editionable or if all editions must have the same edition of the view it's noneditionable.
Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Difference between editionable and editioning views? Ask Question. Asked 10 months ago. Active 10 months ago. Viewed times. The documentation contains a full explanation of editioning views, but you can see how they can be used in the examples below. The presence of an editioning view displaying a different representation of a base table may present a bit of a problem when DML is applied to it.
A forward crossediton trigger takes data from the columns in the old edition and transforms it to the that required by the new edition, while reverse crossedition triggers do the opposite.
This means that both editions can be used simultaneously without causing logical corruption of the data in the base table. There is a lot of detail about crossedition triggers in the documention , but you can see how they can be used in Example 2 below.
Preparing an existing application for editioning will require a little downtime, but all subsequent upgrades will be free of downtime. The steps involved in this preparation are:. In this example we add a new column to a base table, but want to run the existing application code against an editioning view with the new column omitted, and a new version of the application code against the amended table.
First, we connect to the user, check the session edition and create the base table, editioning view and procedure that represents our application. Now switch to the new edition and run the procedure again, just to prove it has been inherited by the new edition.
Next we switch to the new edition and create an editioning view to reflect the new table structure. We can now compile and test the new version of the procedure against the modified table editioning view. So we have a single base table, being accessed by two different versions of the same application in a single schema. The version of the application used depends on the sessions edition setting. AS subquery. Specify a subquery that identifies columns and rows of the table s that the view is based on.
The select list of the subquery can contain up to expressions. Restrictions on the Defining Query of a View The view query is subject to the following restrictions:. For object views, the number of elements in the subquery select list must be the same as the number of top-level attributes for the object type. The data type of each of the selecting elements must be the same as the corresponding top-level attribute.
Notes on Updatable Views The following notes apply to updatable views:. An updatable view is one you can use to insert, update, or delete base table rows. The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:.
Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause an unnested collection , then the view is not inherently updatable.
In addition, if an inherently updatable view contains pseudocolumns or expressions, then you cannot update base table rows with an UPDATE statement that refers to any of these pseudocolumns or expressions. If you want a join view to be updatable, then all of the following conditions must be true:. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view. Oracle Database Administrator's Guide for more information on updatable views.
There is a subquery within the defining query of this view or any view on which this view is based or. The view shows the employees in department 20 and their annual salary:. You can use this view to isolate an application from DDL changes to the orders table during an administrative operation such as an upgrade. You can create a DML trigger on this view, so that the trigger fires when a DML operation targets the view itself, but does not fire if the DML operation targets the orders table.
Creating a View with Constraints: Example The following statement creates a restricted view of the sample table hr. Creating an Updatable View: Example The following statement creates an updatable view named clerk of all clerks in the employees table. Only the employees' IDs, last names, department numbers, and jobs are visible in this view, and these columns can be updated only in rows where the employee is a kind of clerk:. You cannot subsequently insert a new row into clerk if the new employee is not a clerk.
0コメント