Thursday, August 13, 2015

Using conditional compilation for APEX dependent code

I'm currently working on a new release of my PPTX Creator Package.
While adding parts which would only compile if APEX is installed in the database I thought maybe it's a bad idea to introduce a hard dependency as a lot of the code doesn't need APEX installed.
So I needed to come up with a way to allow installation without APEX, but also enable additional functions when APEX is installed.
The options I checked where:
  1. Split into APEX-specific and generic package
  2. Offer two versions of the package
  3. Use conditional compilation
From the titel you might already know which option I chose, but let's briefly look at the implications of the options.

Option 1:
Splitting into two packages seemed like an immediate win as it feels natural for the use case. However when looking a bit further it could lead to the need of exposing internal parts of the generic package.
Running on 12c you could use the ACCESSIBLE BY clause to mitigate, but not everyone is on 12c (yet). A good blog post on the ACCESSIBLE BY clause can be found here.

Option 2:
I immediately moved over that option, just imagine the maintenance effort:
It would be a like fixing two different packages with a lot of copy-and-paste code back and forth.
No I don't want that!

Option 3:
I decided to use conditional compilation for the APEX specific features. For those of you who don't know conditional compilation yet, have a look at this Oracle Magazine Article by Steven Feuerstein from 2006 and also check out the links in that article.
In short conditional compilation allows you to exclude or include parts of the package during compilation based on conditions specified. A typical use case for conditional compilation is having one package which conditionally compiles with different code based on the Oracle Release.

So I had decided to use conditional compilation, but then I realized that I hadn't thought of how to determine if APEX is installed. Unlike the version use case, for which the database provides the DBMS_DB_VERSION package, there is no such thing for verifying installed components.
So let's start with how to generally check if APEX is installed.
If you have DBA rights you can use following query:
SELECT comp_name, version, status
  FROM dba_registry
 WHERE comp_name = 'Oracle Application Express'
;

Without DBA rights it's a bit different, as there is no ALL_REGISTRY table in the dictionary. There is a USER_REGISTRY table but that doesn't help either.
However there is something called ALL_REGISTRY_BANNERS and we can use that one with any user. To check if APEX is installed simply run the following query:
SELECT banner
  FROM all_registry_banners
 WHERE banner LIKE 'Oracle Application Express%'
;

Now let's move on to finding a means to use the query result for controlling the conditional compilation. You can also use user-defined flags for controlling the compilation process, below show how to set a flag called apex_installed for your session.
ALTER SESSION SET plsql_ccflags = 'apex_installed:1';
You can reference this flag in conditional compilation blocks within your code.
To recap, we now have a means to check whether APEX is installed and we are able to set a flag to be used by the conditional compilation.
Putting everything together I ended up with an install script similar to the code code.
SET define '^'
define HASAPX = '0'
column apx new_val HASAPX

SELECT '1' AS apx
  FROM all_registry_banners
 WHERE banner LIKE 'Oracle Application Express'
   AND ROWNUM = 1
;

ALTER SESSION SET plsql_ccflags = 'apex_installed:^HASAPX';
@@install_package.sql

Within the package you surround the code blocks with something like
$IF $$apex_installed = 1 $THEN
-- Put APEX specific code here
$END
To catch the case when the flag isn't set before compilation include an error clause like below
  $IF $$apex_installed IS NULL $THEN
    $ERROR 'Set CCFLAG apex_installed to either 0 or 1.' $END
  $END

That's all for now folks, but stay tuned for the next release of my PPTX Creator package with much enhanced functionality.