Examples of Conditional Compilation - Oracle

Let’s start with some examples of several types of conditional compilation.

Use application package constants in $IF directive

The $IF directive can reference constants defined in your own packages. In the example below, I vary the way that the bonus is applied depending on whether or not the location in which this third-party application is installed is complying with the Sarbanes-Oxley guidelines. Such a setting is unlikely to change for a long period of time. If I rely on the traditional conditional statement in this case, I will leave in place a branch of logic that should never be applied. With conditional compilation, the code is removed before compilation.

  /* File on web: cc_my_package.sql */
  CREATE OR REPLACE PROCEDURE apply_bonus (
    id_in IN employee.employee_id%TYPE
   ,bonus_in IN employee.bonus%TYPE)
  IS
  BEGIN
     UPDATE employee
        SET bonus =
         $IF employee_rp.apply_sarbanes_oxley
         $THEN
            LEAST (bonus_in, 10000)
         $ELSE
            bonus_in
         $END
       WHERE employee_id = id_in;
    NULL;
  END apply_bonus;
  /

Toggle tracing through conditional compilation flags

We can now set up our own debug/trace mechanisms and have them conditionally compiled into our code. This means that when our code rolls into production, we can have this code completely removed, so that there will be no runtime overhead to this logic. Note that I can specify both Boolean and PLS_INTEGER values through the special PLSQL_CCFLAGS compile parameter.

  /* File on web: cc_debug_trace.sql */
  ALTER SESSION SET PLSQL_CCFLAGS = 'oe_debug:true, oe_trace_level:10';

  CREATE OR REPLACE PROCEDURE calculate_totals
  IS
  BEGIN
  $IF $$oe_debug AND $$oe_trace_level >= 5
  $THEN
     DBMS_OUTPUT.PUT_LINE ('Tracing at level 5 or higher');
  $END
     NULL;
  END calculate_totals;
  /