As part of a larger digital transformation strategy, a Nuvalence client has a very large Oracle database that they would like to move to RDS in the AWS cloud. Migrating legacy databases that use PL/SQL extensively can be a challenge. In this case, much of the application code is written in stored procedures and uses Oracle features in ways that will not work in the AWS environment. The first solution that comes to mind is to migrate those problematic functions to another part of the application that is not written in PL/SQL, like the services written in Java. The problem with this approach is the sheer number of functions that have this issue. The core of this application has been running for 20 years, and there are three quarters of a million lines of PL/SQL running in the database. Rewriting all of these functions and migrating them to the service layer would take time that we just don’t have.  Wherever possible, we need to find solutions that work within PL/SQL with minimal modification to the existing code.

Before evaluating solutions, let’s set a little bit of context:

• The application has a large set of PL/SQL functions that create output files that are shared with downstream applications

• The output files are shared via a mounted filesystem

• AWS RDS Oracle instances support writing files locally, but these local file folders are internal to the RDS instance and cannot be shared externally with other applications

We need a way to share input and output files with the outside world, and we chose AWS S3 to be the new repository for shared data. We want to begin refactoring these PL/SQL functions to use S3 immediately while the database is still running on-premise so that we can deploy the myriad of integration changes in phases, but there are a few issues to resolve.   The on-premise and AWS Oracle environments impose different requirements for the functions we need to change, like:

• The AWS RDS Oracle environment supports a package to copy files to and from S3 called rdsadmin, but the on-premise version of Oracle does not.

• The on-premise environment would support invoking the aws command line tool to copy a file from S3, but the AWS environment would not.

•  The AWS rdsadmin functions to copy files are asynchronous, and the available on-premise copy methods are synchronous.

So how do we write a single “copy file from S3” function in PL/SQL that does one thing in the on-premise environment, and something else in AWS?  A simple “if” statement won’t work because the AWS version of the code literally won’t compile in the on-premise environment, and vice versa.

Fortunately PL/SQL supports conditional compilation.  With a selection directive, we can evaluate an expression at compile-time to determine which block of source code should be included in the compiled code.  We’ve set a constant to flag which environment we are running in, and use that to determine whether we need to compile the AWS or on-premise versions of the S3 functions. This allows us to keep both versions of the S3 functions in a single package, and allows us to leave the client code unchanged when switching environments.

The syntax for a selection directive is fairly straightforward:

$IF <constant boolean expression> $THEN
  <code block A>
$ELSE
    <code block B>
$END

At first glance you might mistake it for a regular “if” statement if you didn’t notice the dollar signs, but it behaves very differently. The block of code on the “false” side of the directive isn’t skipped at run time, it is ignored completely by the compiler at compile time, like a comment would be.  The boolean expression can use one of a set of predefined Oracle constants, a user-defined PLSQL_CCFLAG passed into the compiler, or a regular constant expression. This series of blog posts goes into more detail.

Conditional compilation has other uses, like allowing us to exclude debugging code in production environments, or compile different code based on what Oracle version we’re running. Conditional compilation is one of those features that you can go an entire career without finding a use for, but every once in a while, it helps solve a difficult problem.