One of my pet-projects is LoggerUtil, which is a utility for Logger, which is an excellent logging tool for PL/SQL.
This post is not about Logger, but some dealings with Conditional Compilation.
With Conditional Compilation you can create a single code base to handle different functionalities depending on compiler flags.
The latest addition to LoggerUtil was a method to create a custom template. For this to work, LoggerUtil depends on a certain Logger Release (where issue #103 is implemented). The dependency lies in the fact that the custom template is stored in the LOGGER_PREFS table and before issue #103 was resolved there was no way to add data to the LOGGER_PREFS table (or at least not a supported way).
Conditinal Compilation is just what the doctor ordered. With a Conditional Compilation directive you can check if Logger is at least version 3, so we can have a supported way of writing into the LOGGER_PREFS table. Sounds easy enough.
And this is where I made some discoveries about Conditional Compilation.
Let's begin with a package specification with only CONSTANTS in there.
create or replace package constants_pkg is version constant varchar2(10) := '1.2.3'; major_num constant number := 1; major_int constant pls_integer := 1; major_vc constant varchar2(1) := 'a'; end constants_pkg;There are a few variations in there, starting with the current method that Logger has implemented the version number (the constant called VERSION).
Second there is a NUMBER constant.
Third is an PLS_INTEGER constant.
Fourth a variation to the first constant, just one character.
Following is a procedure, called conditional (how appropriate):
create or replace procedure conditional is begin $if constants_pkg.version like '1%' $then dbms_output.put_line ('string, with LIKE comparison'); $end dbms_output.put_line ('This will always be displayed'); end conditional;The $IF, $THEN, $END are part of the syntax used for Conditional Compilation.
On line 5 the packaged constant is checked if the string start with a 1. When it does, line 7 is included in the compiled code. If the packaged constant doesn't start with a 1 then line 7 is not included in the compiled code.
You might say: "Should you do a comparison like this"
$if to_number (substr (constants_pkg.version, 1, 1)) > 1and you would be right, but... for this example it doesn't matter as both don't work. When you try to compile the code, you will see the following error:
Errors for PROCEDURE CONDITIONAL: LINE/COL ERROR -------- ----------------------------------------------------------------- 4/8 PLS-00174: a static boolean expression must be used
So my next attempt at getting this to work, was using the full version constant:
$if constants_pkg.version = '1.2.3'With the same results, the same compilation error.
What about just a single character string?
$if constants_pkg.major_vc = '1'...Nope, again the same compilation error.
Next up, try a NUMBER constant instead:
$if constants_pkg.major_num = 1.0I thought the ".0" at the end could make a difference, but alas.. same compilation error.
Last attempt: the PLS_INTEGER:
$if constants_pkg.major_int = 1This may not come as a surprise now, but this works. :D
This is similar to the way that Oracle does it itself.
When you want to know which release of the Oracle database you are on, you can check DBMS_DB_VERSION. There are constants defined in DBMS_DB_VERSION which you can use with Conditional Compilation.
So Martin, if you are still reading: Can I have the version as a PLS_INTEGER, please?