Writing code to support multiple versions of Oracle Database

3rd in a series on conditional compilation. See end of post for links to all posts in the series.

Do you write code that must run on more than one version of Oracle Database? This is almost always the case for suppliers of "off the shelf" applications. And when confronted with this reality, most developers choose between these two options:

Use only those features available in all versions ("lowest common denominator" or LCD programming).

or

Maintain separate copies of the code for each supported version, so you can take advantage of new features in later versions of the database ("sure to create a mess" or SCAM programming).

And let's face it, both have some serious drawbacks.

The LCD approach ensures that your code will compile on all supported versions. But you will sacrifice the ability to take advantage of new features in the later versions. That can be a high price to pay.

The SCAM approach, well, "sure to create a mess" says it all. What's the chance that you will be able to keep 2, 3, or 4 copies of the same code up to date with all bug fixes, enhancements, comments, etc., along with the special-purpose code written to leverage features in that specific version?

Fortunately, there is a third and better way: use conditional compilation so that you can write and maintain your code in a single file / program unit, but still take maximum advantage of each version's cool new features.

Actually, you use conditional compilation and the DBMS_DB_VERSION package. Let's check them out!

Here's the entire code for the DBMS_DB_VERSION package for Oracle Database 12c:

PACKAGE DBMS_DB_VERSION IS
   VERSION CONSTANT PLS_INTEGER := 12; -- RDBMS version number
   RELEASE CONSTANT PLS_INTEGER := 2;  -- RDBMS release number
   ver_le_9_1    CONSTANT BOOLEAN := FALSE;
   ver_le_9_2    CONSTANT BOOLEAN := FALSE;
   ver_le_9      CONSTANT BOOLEAN := FALSE;
   ver_le_10_1   CONSTANT BOOLEAN := FALSE;
   ver_le_10_2   CONSTANT BOOLEAN := FALSE;
   ver_le_10     CONSTANT BOOLEAN := FALSE;
   ver_le_11_1   CONSTANT BOOLEAN := FALSE;
   ver_le_11_2   CONSTANT BOOLEAN := FALSE;
   ver_le_11     CONSTANT BOOLEAN := FALSE;
   ver_le_12_1   CONSTANT BOOLEAN := FALSE;
   ver_le_12_2   CONSTANT BOOLEAN := TRUE;
   ver_le_12     CONSTANT BOOLEAN := TRUE;
END DBMS_DB_VERSION;

The package contains two "absolute" constants: the version and release numbers. it then contains a set of "relative" constants, basically telling you, true or false, if the current version is less than or equal to the version specified by the constant name.

If I was a betting man, I'd bet a whole lot of money than you could figure out what this package looks like in Oracle Database 18c and 9c. If not, run this query:

  SELECT LPAD (line, 2, '0') || ' - ' || text
    FROM all_source
   WHERE owner = 'SYS' AND name = 'DBMS_DB_VERSION'
ORDER BY line

One Program for Multiple Versions
The DBMS_DB_VERSION package makes it really easy to ensure that each installation of your code takes full advantage of the latest and greatest features.

Consider the package body below (full code available on LiveSQL). Starting with Oracle Database 10g Release 2, you can use INDICES OF with FORALL to handle spare bind arrays elegantly. Prior to that, you would have to "densify" the collection and get rid of any gaps.

A simple application of the $IF statement along with a reference to the appropriate DBMS_DB_VERSION constant, and job done.

CREATE OR REPLACE PACKAGE BODY pkg
IS
   PROCEDURE insert_rows ( rows_in IN ibt )
   IS
   BEGIN
$IF DBMS_DB_VERSION.VER_LE_10_1
$THEN
   /* Remove gaps in the collection */
   DECLARE
      l_dense t;
      l_index PLS_INTEGER := rows_in.FIRST;
   BEGIN
      WHILE (l_index IS NOT NULL)
      LOOP
         l_dense (l_dense.COUNT + 1) := rows_in (l_index);
         l_index := rows_in.NEXT (l_index);
      END LOOP;
      
      FORALL indx IN l_dense.FIRST .. l_dense.LAST
         INSERT INTO t VALUES l_dense (indx);
   END;
$ELSE
      /* Use the very cool INDICES OF feature to skip over gaps. */
      FORALL indx IN INDICES OF rows_in
         INSERT INTO t VALUES rows_in (indx);
$END
   END insert_rows;
END;

Not Just for Oracle Versions
You can use this same technique to manage deployments of different versions of your code (different for different versions of your application or different for different customers). You can create your own variation on DBMS_DB_VERSION, or use your own flags, and use it in exactly the same way in your code base.

You will just need to make that your version package is always available, or your flags are always set so that you end up with the right code for the right customer.

In addition, you can only use constants with Boolean or integer values in your $IF conditions., and those conditions must be static expressions, meaning their values do not vary when initialized in the package.

Perhaps an example would help here. :-)

Suppose you wanted to use conditional compilation to automatically enable or disable features in the application depending on the customer.

You might create code like this:

CREATE OR REPLACE FUNCTION eligible_for_use (type_in       IN VARCHAR2,
                                             customer_in   IN VARCHAR2)
   RETURN BOOLEAN
   AUTHID DEFINER
IS
BEGIN
   RETURN type_in = 'TURBO' AND customer_in = 'ACME Inc';
END;
/

CREATE OR REPLACE PACKAGE include_features
   AUTHID DEFINER
IS
   include_turbo   CONSTANT BOOLEAN := eligible_for_use ('TURBO', USER);
END;
/

CREATE OR REPLACE PROCEDURE go_turbo
   AUTHID DEFINER
IS
BEGIN
   $IF include_features.include_turbo
   $THEN
      DBMS_OUTPUT.put_line ('all systems go');
   $ELSE
      NULL;
   $END
END;
/

So far, so good. But when I try to apply it, I see this error:

CREATE OR REPLACE PROCEDURE go_turbo
   AUTHID DEFINER
IS
BEGIN
   $IF include_features.include_turbo
   $THEN
      DBMS_OUTPUT.put_line ('all systems go');
   $ELSE
      NULL;
   $END
END;
/

PLS-00174: a static boolean expression must be used

The value of the include_turbo constant is not set until the package is initialized, and that's too late when it comes to conditional compilation.

So if you want to take this approach, you will need to generate (or hand-code, but that seems like a stretch) the (in this case) include_features package so that each customer receives its own version of the package, as in:

CREATE OR REPLACE PACKAGE include_features
   AUTHID DEFINER
IS
   /* Generated 2019-04-06 13:44:50 for ACME Inc - Customer ID 147509 */
   include_turbo   CONSTANT BOOLEAN := TRUE;
END;
/

and then when go_turbo and other program units are compiled at the customer site, the correct features will be made available.
Tips for Doing It Right
Suppose I want the following procedure to compile and run on versions 12.2, 18.x and 19.x. Will it work as desired?

CREATE OR REPLACE PROCEDURE lots_of_versions
   AUTHID DEFINER
IS
BEGIN
   $IF dbms_db_versions.ver_le_19 AND NOT dbms_db_versions.ver_le_18
   $THEN
      DBMS_OUTPUT.put_line ('Having fun on 19c!');
   $ELSIF dbms_db_versions.ver_le_18 AND NOT dbms_db_versions.ver_le_12
   $THEN
      DBMS_OUTPUT.put_line ('Good to go on 18.x');
   $ELSIF NOT dbms_db_versions.ver_le_12_1
   $THEN
      DBMS_OUTPUT.put_line ('Good to go on 12.2');
   $ELSE
      raise_application_error (-20000, 'Not supported');
   $END
END;
/

No - when I try to compile this on 12.2, 18.1 and 18.2, it will fail because those 19c-related constants are not defined in the earlier versions of the DBMS_DB_VERSION package.

This approach will work much better:

CREATE OR REPLACE PROCEDURE lots_of_versions
   AUTHID DEFINER
IS
BEGIN
   $IF dbms_db_versions.ver_le_12_2
   $THEN
      raise_application_error (-20000, '12.1 is not supported');
   $ELSIF dbms_db_versions.ver_le_18_1
   $THEN
      DBMS_OUTPUT.put_line ('Good to go on 12.2');
   $ELSIF dbms_db_versions.ver_le_19_1
   $THEN
      DBMS_OUTPUT.put_line ('Good to go on 18.x');
   $ELSE
      DBMS_OUTPUT.put_line ('Having fun on 19c!');
   $END
END;
/

In other words, go from lowest version to highest version in any $IF statements to ensure that the referenced constant will always be defined (or never reached).

Conditional Compilation Series
1. An introduction to conditional compilation
2. Viewing conditionally compiled code: what will be run?
3. Writing code to support multiple versions of Oracle Database
4. Setting and using your own conditional compilation flags
5. How to make sure your code FAILS to compile