Oracle PL/SQL Best Practices

Name

PKG-07: Insulate applications from Oracle version sensitivity using version-specific implementations.

Synopsis

Many organizations need to write code that will run on different Oracle versions (such as Oracle 7.3 and Oracle 8.1). There are two approaches you might follow in this situation:

• Use “lowest common denominator” features that are available in all versions.

• Use the best and most appropriate features available in each version.

If you take the first approach, you can maintain just one version of code, but you will also sacrifice significant functionality and performance advantages. If you take the second approach, you can avoid maintaining multiple copies of the code by (a) using packages to isolate those differences, and (b) relying on the separation of package specification and body to “execute around” compilation errors.

Here are the basic steps you need to take to achieve this effect:

1. Extract all version-specific logic into separate package bodies, separated by database version.

2. Create a function that returns the current Oracle version.

3. Modify or create the main (public) package to call each of the version-specific programs, based on the current Oracle version.

4. Compile and use the code in each different database version.

This last point is, in a way, the most interesting. You see, at least one of your package bodies will actually fail to compile—and you won’t care! The package body for Oracle8i, for example, doesn’t compile in Oracle 7.3. But that doesn’t matter at all, ...