Writing Code to Support Multiple Versions of Oracle Database
See how to write code to support multiple versions of Oracle Database.
Join the DZone community and get the full member experience.Join For Free
3rd in a series on conditional compilation.
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).
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).
Published at DZone with permission of Steven Feuerstein, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.