deploying multiple versions of the same views in Oracle [closed]

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

This question does not appear to be about programming within the scope defined in the help center.

Closed 9 years ago.

Improve this question

Any anecdotal comment开发者_开发知识库s or suggestions are welcome.

We have applications that consume 3rd party vendor data through views. The business has requested that our applications bound to prior versions of the views should be able to continue using them rather than having to sync-up with every updated vendor release of the views. Application "A" should be able to use v.1.1 of the views while application "B" uses v.1.2 of the views, both of which interact with the same tables in the global schema / namespace.

People have recommended naming the views with the release number but that seems cumbersome to applications that do keep in sync with updated releases of the views. Is there a better solution to this problem? Perhaps keeping each supported release of the views in it's own schema and having the views pull from the global schema where the tables are defined and where the data is?

---

If you are maintaining a view layer, you would normally maintain compatibility between releases by limiting your v1.2 changes to adding additional columns to existing views or adding additional views. Applications that didn't want to upgrade would continue to use the existing columns of the existing views while applications that do want to upgrade could use the new views. The Oracle data dictionary views are a great example of this approach. In every new release, there are dozens of new views that expose new functionality for applications that need it. But scripts that were written against the data dictionary views in Oracle 7 will happily run against an 11.2 database (less efficiently, perhaps).

This is really no different than maintaining any other sort of API. You don't generally take away existing API calls or force users to pass additional parameters to an API when you release the 1.2 version. Instead, you grandfather the old API calls into the new version. From time to time, of course, you may have to deprecate some part of the API and force existing applications to make a small upgrade. But those are relatively rare and require only that the application change the code making that one deprecated call-- the application doesn't need to completely upgrade to the new v1.2 API.

---

If you're on 11gR2 you should check out Edition-Based Redefinition. It does pretty much what you want, and a whole lot else besides. Find out more.

---

You could opt to abstract your codes name of the view from the vendors using synonyms for the views, so your app consumes a specific name, whilst the actual view the data is being pulled from changes. If you want to avoid synonym use, then create your own view selecting from the appropriate view from the vendor, same effect but without synonyms.

---

You've essentially listed your two feasible options: either name the views with version-specific names or use version-specific schemas. Either way, you'll need to copy off the view definition and recreate it for the version-specific applications.

Assuming that you're truly interested in maintaining a complete copy of the view, this shouldn't be too much of a headache.