Feuertip #34: conditional compilation versioning for APEX! (and your product, too)

Conditional compilation is a very cool but rarely used feature of PL/SQL that allows you to tell the compiler to include or exclude (ignore) chunks of code based on the values of conditional compilation flags or package-based static expressions.

OK, sure, that’s a mouthful. And it’s a tricky feature to implement. That’s why I wrote a five-part series on conditional compilation back in 2019. So no need to repeat all of that here, right? I did, however, copy/paste the resources I listed in the first article to the end of this post. Because I am that nice a guy.

🙂

Instead, here I want to focus on what is probably the most important use case of conditional compilation: compile and run your PL/SQL code on different versions of Oracle, taking full advantage of features specific to those versions.

In other words, you write code that has to be able to run on more than one version of Oracle Database. You don’t want to take the least common denominator approach (can’t take advantage of newer features), and you don’t want to maintain multiple copies of your code, one for each supported version.

With conditional compilation, you can write code like this:

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;

This code recognizes that as of 10.2, the indices of feature allows us to declaratively “skip over” undefined index values, so no need to compress the collection, which is what all that code does inside the “less than or equal to 10.1” branch.

This is possible because each version of Oracle Database comes with its own particular version of the dbms_db_version package, with all the boolean static expressions (constants) set appropriately.

I recently ran into the need to do something similar with Oracle APEX. I am building a new utility for Insum consultants called Insum Mail. It adds all sorts of email-related features to APEX applications by building a layer of code (and APEX apps) around apex_mail.

One of the features of Insum Mail is 100% compatibility with the apex_mail package: do a global search and replace of “apex_mail” with “insum_mail” and voila! All your code compiles and continues to work, and you can take advantage of all the other wonderful stuff packed into Insum Mail.

But! The apex_mail API changes over time. In fact, while I was developing the utility on 20.2, Oracle APEX 21.1 was released – and it added a new overloading of add_attachment (for clobs) and some other changes to parameter lists.

No problem, right? Just use some conditional compilation statements like this:

   $if oracle_apex_version.ver_le_20 $then  
   $elsif oracle_apex_version.ver_le_21_1 $then 
   $else
   procedure add_attachment (
      p_mail_id    in number,
      p_attachment in clob,
      p_filename   in varchar2, 
      p_mime_type  in varchar2
   );
   $end

Great idea, Steven! Just one problem: (boo hoo!) APEX does not come with its own versioning package.

Fortunately, this turned out to be one of those problems that could be solved by moi (aka, me).

Here it is:

declare
   vr  number; v   number; r   number;
   blk varchar(4000);

   procedure add_line ( p_line in varchar2 ) is
   begin
      blk := blk  || case when blk is not null then chr(10) end || p_line;
   end;

   function add_bool ( p_current_version in number, p_a_version in number ) return varchar2 is
   begin
      return case when p_current_version <= p_a_version then 'true' else 'false' end;
   end;
begin
   select to_number(substr(version_no, 1, instr(version_no, '.', 1, 2) - 1)),
          to_number(substr(version_no, 1, instr(version_no, '.', 1, 1) - 1)),
          to_number(substr(version_no, instr(version_no, '.', 1, 1) + 1, instr(version_no, '.', 1, 1) - 2))
     into vr,
          v,
          r
     from apex_release;
   add_line(
      'create or replace package oracle_apex_version authid definer is 
   version constant pls_integer := '
      || v
      || '; release constant pls_integer := '
      || r
      || '; '
   );
   for yr in 18..v loop
      add_line('ver_le_' || yr || ' constant boolean := ' || add_bool(trunc(vr), yr) || ';');
      add_line('ver_le_' || yr || '_1 constant boolean := ' || add_bool(vr, yr +.1) || ';');
      add_line('ver_le_' || yr || '_2 constant boolean := ' || add_bool(vr, yr +.2) || ';');
   end loop;
   add_line(' end;');
   execute immediate blk;
end;
/

I run this block of code as a supporting object installation script. It generates and compiles a package that looks like this (for 20.2 anyway):

create or replace package oracle_apex_version authid definer is 
   version constant pls_integer := 1; 
   release constant pls_integer := 2; 
   ver_le_18_1  constant boolean := false;
   ver_le_18_2  constant boolean := false;
   ver_le_19    constant boolean := false;
   ver_le_19_1  constant boolean := false;
   ver_le_19_2  constant boolean := false;
   ver_le_20_1  constant boolean := false;
   ver_le_20    constant boolean := true;
   ver_le_20_2  constant boolean := true;
end;

And now I can do versioning of my APEX-based code using conditional compilation!

But wait, there’s more!

I realize that most of you will never need to build database or APEX code that is installed on multiple versions of those products.

But what about your own products? Do you have APIs that change over time? Do you need to be able to support your own product across installations of various versions of that product?

Then I’ve got a treat for you! I’ve generalized the above block into some code you can use to generate a versioning package for any product that has a function which returns its version in the form V.R (version.release, as in 21.2). Check it out on LiveSQL!

Resources

Comprehensive white paper: a great starting place – and required reading – for anyone planning on using conditional compilation in production code

Conditional compilation scripts on LiveSQL

Tim Hall (Oracle-BASE) coverage of conditional compilation

Conditional compilation documentation

My Oracle Magazine article on this topic