Conditional Compilation in PLSQL

Question:
What is PL/SQL conditional compilation, how does it work, and how can I learn more about it?
Answer:
This question was answered in the July/August 2006 issue of Oracle Magazine.
Oracle Database 10g has been groundbreaking when it comes to the world of PL/SQL development, most notably in the evolution of the PL/SQL compiler. This PL/SQL Practices column focuses on the Oracle Database 10g Release 2 feature known as conditional compilation.
Oracle Database 10g Release 1 offered an optimizing compiler and compile-time warnings. The PL/SQL optimizer automatically transforms code so that it can run more efficiently (this optimizer is different from the cost-based optimizer, which Oracle Database uses to optimize the execution of SQL statements). Oracle estimates that you can expect to see your PL/SQL statements execute, on average, in half the time they took in earlier versions of Oracle Database. (Note that the PL/SQL compiler affects only PL/SQL statements, not SQL statements, within your programs).
The compile-time warnings give you feedback on programs that might compile but could still be improved. These warnings include recommendations on how and when to apply the NOCOPY parameter hint to reduce parameter-passing overhead, identify functions that contain one or more branches of logic that will not execute a RETURN statement, and much more.
Oracle Database 10g Release 2 takes PL/SQL another big step forward, by adding support for conditional compilation. We can now use conditional compilation to direct the compiler to conditionally include or exclude selected parts of a program, based on conditions specified with conditional compilation directives, statements, and identifiers prefixed by a dollar sign ($).
One particularly nice aspect of conditional compilation is that text that is excluded during conditional compilation need not be legal PL/SQL; this gives you tremendous flexibility when writing programs that need to work differently in different versions of Oracle Database.
There are many ways PL/SQL conditional compilation can benefit your application development process. The following are a few of the most common ways to apply PL/SQL conditional compilation to your applications:
• Write a single program unit that automatically takes advantage of version-specific features of Oracle Database. You no longer need to maintain the same basic logic in multiple files, one for Oracle9i Database and another for Oracle Database 10g, or rely on SQL*Plus substitution variable logic to achieve the same effect.
• Run or expose portions of your code during testing, and then hide that code when it goes into production. This technique is particularly valuable for tracing code execution and unit-testing private package subprograms.
• Improve the maintainability of your PL/SQL code base, by soft-coding elements of your application that would otherwise have to be hard-coded as literals.
To take advantage of conditional compilation, add compiler directives (commands) to your code. The PL/SQL compiler will then evaluate the directives before compilation occurs and determine which parts of your program text cause the generation of executable code. The modified source code is then passed to the compiler for compilation.
There are three types of directives:
Selection directives. Use the $IF directive to evaluate expressions and determine which code should be included in the compiled code.
Inquiry directives. Use the $$ identifier syntax to refer to conditional compilation flags. These inquiry directives can be referenced within an $IF directive or used independently in your code.
Error directives. Use the $ERROR directive to report compilation errors based on conditions evaluated when Oracle Database prepares your code for compilation.
Oracle has also added two packages to support conditional compilation: the DBMS_DB_VERSION package, which you can use to analyze the version (in both absolute and relative terms) of the instance of Oracle Database to which you are connected, and the DBMS_PREPROCESSOR package, which allows you to see your code after all conditional compilation directives have been executed.
Availability of conditional compilation. PL/SQL conditional compilation is available in Oracle Database 10g Release 1 and later (from 10.1.0.4 onward). In Oracle Database 10g Release 2, conditional compilation is enabled by default, and it cannot be disabled.
In Oracle Database 10g Release 1, conditional compilation is enabled by default, but you can disable it by setting an underscore parameter. Conditional compilation is also supported in Oracle9i Database Release 2 (from 9.2.0.6 onward); it is disabled by default, but you can enable it by setting an underscore parameter.
In order to disable conditional compilation in Oracle Database 10g Release 1 or enable it in Oracle9i Database, contact Oracle Support for information on the conditional compilation underscore parameter.
Get more information on conditional compilation. This column offers some insights into conditional compilation, by answering reader questions. You will certainly want to study this feature in more depth before applying it to your application code base. I suggest that you check out the following:
• Conditional Compilation in Oracle Database 10g Release 2. Hands down the best elaboration of conditional compilation, this white paper by PL/SQL Product Manager Bryn Llewellyn offers 96 pages of detailed explanations and examples, including a case study of how to use conditional compilation with unit testing. Find it at oracle.com/technology/tech/pl_sql/pdf/Plsql_Conditional_Compilation.pdf.
• Oracle documentation on conditional compilation. Oracle Database PL/SQL User's Guide and Reference is available at download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#BABIHIHF.
• My PL/SQL programming book. Oracle PL/SQL Programming, 4th Edition, by Steven Feuerstein with Bill Pribyl: Chapter 20, "Managing PL/SQL Code," explores conditional compilation and offers several examples.