PL/SQL Conditional Compilation

Posted on by

I’ve been going through the motions of database upgrades at work. From Oracle 12.2 onwards, Oracle made changes to the call interface for a number of their in-built packages that involve encrypted external communication (utl_tcp, utl_http, utl_smtp, etc). For example, the call to the routine utl_http.begin_request has gone from:

UTL_HTTP.BEGIN_REQUEST (
   url               IN  VARCHAR2,
   method            IN  VARCHAR2 DEFAULT 'GET',
   http_version      IN  VARCHAR2 DEFAULT NULL,
   request_context   IN  request_context_key DEFAULT NULL)
RETURN req;

to:

UTL_HTTP.BEGIN_REQUEST (
   url               IN  VARCHAR2,
   method            IN  VARCHAR2 DEFAULT 'GET',
   http_version      IN  VARCHAR2 DEFAULT NULL,
   request_context   IN  request_context_key DEFAULT NULL,
   https_host        IN  VARCHAR2 DEFAULT NULL)
RETURN req;

When creating an https connection there’s a new parameter that needs to be supplied. The change is quite trivial but how should you handle this change from within your source code when you will have databases of different versions? You are probably faced with the scenario that your development and test databases get upgraded some time before your production databases. Development does not stop whilst this is going on. Furthermore, you don’t really want to remember to deploy new versions of code at the point in time of the upgrade as that’s yet another thing to remember within an already complicated process. Conditional compilation offers us a solution.

Using conditional compilation we can support different code sections at the same time and have Oracle use the one that is appropriate to the database version that is executing the code. For example, I use the above utl_http routines to post to Slack and pre and post 12.2 database versions can be supported using the following:

-- for Oracle 12.1 and earlier
$IF (DBMS_DB_VERSION.VERSION = 12 AND DBMS_DB_VERSION.RELEASE = 1) OR (DBMS_DB_VERSION.VERSION < 11)      
   l_http_request := utl_http.begin_request (utl          => l_slack_webhook_url
                                            ,method       => 'POST'
                                            ,http_version => 'HTTP/1.1');
-- for Oracle 12.2 and above...
$ELSE
   l_http_request := utl_http.begin_request (utl          => l_slack_webhook_url
                                            ,method       => 'POST'
                                            ,http_version => 'HTTP/1.1'
                                            ,https_host   => 'slack.com');
$END

It’s quite easy to see what’s going on in the above code block. There are some lines that commence with $ and these lines are the conditional compilation lines. In this case I have a conditional IF THEN ELSE block that evaluates the database version and will compile the correct version of the utl_http call for that version.

Conditional compilation is another tool for the PL/SQL toolbox that’s quite handy in certain scenarios. The documentation has further suggested uses and the full breadth of functionality available.

Leave a comment