….is that when you really need to, it allows you to turn up your applications instrumentation  to 11.

A 30 second PL/SQL Conditional Compilation overview

PL/SQL Conditional compilation was introduced with Oracle Database 10gR2. The official documentation  explains how to use the Conditional Compilation constructs but doesn’t give too many use cases. Fortunately the Oracle White Paper PL/SQL conditional compilation not only covers how to use Conditional Compilation, it also gives a number of use cases and finishes with an in depth case study.

Conditional Compilation allows you to mark your portions of your code so that a preprocessor can determine what will actually be sent to the compiler.  Rather than bore you with more words lets look at a simple example.

CREATE PROCEDURE cc_intro
IS
BEGIN

   $IF sys.dbms_db_version.ver_le_12_1
   $THEN

      dbms_output.put_line('Can use the 12c new features'); 

   $ELSE 

      dbms_output.put_line('Have to use the work arounds'); 

   $END

END cc_intro;

In this example, if the procedure is compiled on a database running 12.1 the first message will appear in the compiled version of the code otherwise the second message will appear.

On line 5 the Conditional Compilation selection directive begins with $IF. The value tested must resolve to a static boolean value such as literals, inquiry directives or as in this example a package constant. Notice that the selection directive finishes with $END and not END IF. Also there is no semi-colon after $END.

Once the procedure has been compiled, you can view the code as the compiler will “see” it using the supplied dbms_preprocessor.print_post_processed_source

BEGIN

   dbms_preprocessor.print_post_processed_source
   (
      object_type => 'PROCEDURE',
      schema_name => USER,
      object_name => 'CC_INTRO'
   );

END;
/

After running on my Oracle 12.1 database the following is seen.

PROCEDURE cc_intro
IS
BEGIN 

 dbms_output.put_line('Can use the 12c new features'); 

END cc_intro;

As expected, only the first message exists in the compiled code.

The most important reason

Following months (weeks, hours or minutes!!) of the various stages of testing,  new changes have finally made it into production. At first everything is great and is ticking along nicely. Until it doesn’t. Strange things start to happen. That thing that can’t happen is happening. To add to the complexity try as you might the problem appears to happen only in production.

You trace the problem back to a routine where perhaps a XML document is created or a collection is populated. Unfortunately you are unable to see what these normally opaque data structures contains and that information could be key to solving the issue.

A method that I have often seen to get round this problem is to wrap code with a standard IF statement as shown in Procedure B below.

CREATE PACKAGE pkg_no_cc
IS

   PROCEDURE a;

END pkg_no_cc;
/

CREATE OR REPLACE PACKAGE BODY pkg_no_cc
IS

   g_debugging BOOLEAN := FALSE;

   PROCEDURE b
   IS 

      TYPE emps_tbl IS TABLE OF employees%ROWTYPE
         INDEX BY PLS_INTEGER;

      l_xml        XMLTYPE;
      l_collection emps_tbl; 

   BEGIN

      -- Expected business logic is here...
      dbms_output.put_line('This is procedure b');

      IF g_debugging = TRUE
      THEN 

         -- see what is in the collection
         FOR i IN 1 .. l_collection.COUNT()
         LOOP

            dbms_output.put_line('see the output of the collection'); 

         END LOOP; 

         -- view the xml
         dbms_output.put_line('the xml: ' || l_xml.getstringval() );

      END IF; 

   END b; 

   PROCEDURE a
   IS
   BEGIN

      dbms_output.put_line('Calling procedure b');

      b();

   END a;

END pkg_no_cc;
/

If the debugging statement within Procedure B survived a Code Review and made it
into production it would cause an overhead, no matter how small (they all add up) every time
Procedure B was invoked. It also would be unlikely that this would be the only occurrence of this type statement within your production code so the overhead would be significant and hence the resistance to allowing this level of information reaching production.

Here is the same package rewritten to take advantage of PL/SQL Conditional Compilation.
Other than the package name change, the only change is the replacement of the IF statement
in Procedure B with Conditional Compilation selection and inquiry directive. At this point the inquiry directive hasn’t been created.

CREATE PACKAGE pkg_with_cc
IS

   PROCEDURE a;

END pkg_with_cc;
/

CREATE OR REPLACE PACKAGE BODY pkg_with_cc
IS

   PROCEDURE b
   IS 

      TYPE emps_tbl IS TABLE OF employees%ROWTYPE
         INDEX BY PLS_INTEGER;

      l_xml XMLTYPE;
      l_collection emps_tbl; 

   BEGIN

      -- Expected business logic is here
      dbms_output.put_line('This is procedure B');

      $IF $$debuging = TRUE
      $THEN 

         -- see what is in the collection
         FOR i IN 1 .. l_collection.COUNT()
         LOOP

            dbms_output.put_line('see the output of the collection'); 

         END LOOP; 

         -- view the xml
         dbms_output.put_line('the xml: ' || l_xml.getstringval() );

      $END  

   END b; 

   PROCEDURE a
   IS
   BEGIN

      dbms_output.put_line('Calling procedure b');

      b();

   END a;

END pkg_with_cc;
/

Using dbms_preprocessor.print_post_processed_source to look at the compiled code

BEGIN

   dbms_preprocessor.print_post_processed_source
   (
      object_type => 'PACKAGE BODY',
      schema_name => USER,
      object_name => 'PKG_WITH_CC'
   );

END;
/

The output shows the debugging code has been stripped out by the PL/SQL preprocessor.

PACKAGE BODY pkg_with_cc
IS

   PROCEDURE b
   IS 

      TYPE emps_tbl IS TABLE OF employees%ROWTYPE
         INDEX BY PLS_INTEGER;

      l_xml XMLTYPE;
      l_collection emps_tbl; 

   BEGIN

      -- Expected business logic is here
      dbms_output.put_line('This is procedure B'); 

   END b; 

   PROCEDURE a
   IS
   BEGIN

      dbms_output.put_line('Calling procedure b');

      b();

   END a;

END pkg_with_cc;
/

Now lets recompile the package body, adding the inquiry directive, debugging at the same time.

ALTER PACKAGE PKG_WITH_CC COMPILE BODY
   PLSQL_CCFLAGS = 'debuging:TRUE' REUSE SETTINGS
/

The debugging literal matches $$debugging used by the procedure B and it is set to TRUE. I have included “REUSE SETTINGS” because it ensures that existing values for PL/SQL warnings or PL/SQL Optimizer Level are not discarded.

Running the dbms_preprocessor.print_post_processed_source and viewing the compiled version of the code we now see that the debugging code is present and ready to help solve the production issue.

...no changes to the other parts of the package

   PROCEDURE b
   IS 

      TYPE emps_tbl IS TABLE OF employees%ROWTYPE
         INDEX BY PLS_INTEGER;

      l_xml XMLTYPE;
      l_collection emps_tbl; 

   BEGIN

      -- Expected business logic is here
      dbms_output.put_line('This is procedure B'); 

      -- see what is in the collection
      FOR i IN 1 .. l_collection.COUNT()
      LOOP

         dbms_output.put_line('see the output of the collection'); 

      END LOOP; 

      -- view the xml
      dbms_output.put_line('the xml: ' || l_xml.tostringval() );

 END b; 

...

Once finished we can remove the debugging code from production by recompiling the package body but this time setting the debugging flag to FALSE

ALTER PACKAGE PKG_WITH_CC COMPILE BODY
   PLSQL_CCFLAGS = 'debuging:FALSE' REUSE SETTINGS
/

Summary

Just to be clear, using Conditional Compilation as described in this post requires recompilation of production code which is not something that should ever been taken lightly and great care should be exercised whenever doing so.

Having the ability to leave debugging code in Production and enable it on demand, is in my opinion, reason enough to add PL/SQL Conditional Compilation to your developer toolbox. Used judiciously with an instrumented application it further reduces the number of places that bugs can hide.