Deploying multiple versions of the same views in Oracle

Question

Any anonymous comments or suggestions are welcome.

We have applications that consume third-party vendor data through views. The business requested that our applications related to previous versions of the views can continue to use them, and not synchronize with each updated version of the view provider. Application “A” should be able to use v.1.1 views, while application “B” will use v.1.2 views, both of which interact with the same tables in the global schema / namespace.

People recommend calling the views with the release number, but this seems cumbersome for applications that support synchronization with updated versions of the views. Is there a better solution to this problem? Perhaps supporting each supported release of views in its own schema and having views retrieved from the global schema, where the tables are defined and where the data is located?

Answer 1

If you maintain a presentation layer, you usually maintain compatibility between releases, limiting your changes to v1.2 by adding additional columns to existing views or adding additional views. Applications that do not want to update will continue to use existing columns of existing views, while applications that want to update may use new views. Oracle data dictionary representations are a great example of this. Each new release features dozens of new submissions that open up new possibilities for the applications that need it. But scripts written against data dictionary views in Oracle 7 will happily work with 11.2 database (less efficiently, possibly).

This is no different than supporting any other API. Usually you do not distract existing API calls or force users to pass additional API parameters when version 1.2 is released. Instead, you grandfather, the old API, is entering the new version. From time to time, of course, you may have to give up some part of the API and force existing applications to do a small update. But they are relatively rare and only require the application to change the code making this deprecated call - the application does not need to be fully updated to the new API v1.2.

Answer 2

If you are on 11gR2, you should check Redesign based on releases. He does pretty much what you want and more. Find out more .

Answer 3

You can revoke your view codename from suppliers using synonyms for views, so your application consumes a specific name, while the actual data view is extracted from the changes. If you want to avoid using a synonym, create your own view by choosing from the corresponding supplier view the same effect, but without synonyms.

Answer 4

You essentially indicated two possible options: either name views with version-specific names, or use version-specific schemas. In either case, you need to copy the view definition and recreate it for version-specific applications.

Assuming that you are really interested in maintaining a complete copy of the presentation, this should not be too much of a headache.