Conditional compilation allows the compiler to compile selected parts of a program based on conditions you specify using $ syntax in PL/SQL. When you see statements like $IF, $ELSE, $END and $ERROR in your PL/SQL code, you are looking at conditional compilations, sometimes also referred to as "ifdef" processing.
There's a really good chance you've never taken advantage of conditional compilation in PL/SQL, so I thought I'd write up a few blog posts about why you might want to use it - and then how to put it to use.
Conditional compilation comes in very handy when you need to do any of the following:
- Compile and run your PL/SQL code base on different versions of Oracle, taking advantage of features specific to those versions.
- Run certain code during testing and debugging, but then omit that code from the production code. Or vice versa.
- Install/compile different elements of your application based on user requirements, such as the components for which a user is licensed.
- Expose usually private subprograms in the package specification to allow for direct testing on those subprograms.
When your program is compiled, the PL/SQL preprocessor evaluates the directives and selects those portions of your code that should be compiled. This pared-down source code is then passed to the compiler for compilation.
The preprocessor checks the value of the database parameter, PLSQL_CCFLAGS, to see if any application-specific conditional compilation flags have been set.
There are three types of directives:
Use the $IF directive to evaluate expressions and determine which code should be included or avoided.
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.
Use the $ERROR directive to report compilation errors based on conditions evaluated when the preprocessor prepares your code for compilation.
I'll show you a simple example of each of these directives, then point you to additional resources. Future blog posts will go into detail on specific use cases, as well as two packages related to conditional compilation, DBMS_DB_VERSION and DBMS_PREPROCESSOR.
In the following block, I use $IF, $ELSE and DBMS_DB_VERSION to determine if I should include the UDF prima (new to Oracle Database 12c), which improves the performance of functions called from within SQL statements:
Next up: use my own application-specific inquiry directive, along with one provided by Oracle:
CREATE OR REPLACE FUNCTION my_function (n IN NUMBER) RETURN VARCHAR2 IS $IF DBMS_DB_VERSION.VER_LE_11_2 $THEN /* UDF pragma not available till 12.1 */ $ELSE PRAGMA UDF; $END BEGIN RETURN TO_CHAR (n); END; /
Finally, I use $ERROR to force a compilation error if anyone tries to compile this code on a version earlier than 12.1.
ALTER SESSION SET PLSQL_CCFLAGS = 'commit_off:true' / CREATE OR REPLACE PROCEDURE flexible_commits IS BEGIN $IF $$commit_off $THEN DBMS_OUTPUT.PUT_LINE ('Commit disabled in $$PLSQL_UNIT'); $ELSE COMMIT; $END END; /
CREATE OR REPLACE PROCEDURE uses_the_latest_and_greatest AUTHID DEFINER IS BEGIN $IF DBMS_DB_VERSION.VER_LE_12_1 $THEN $ERROR 'This program requires Oracle Databse 12.1 or higher.' $END $END NULL; END; /
Comprehensive white paper: a great starting place - and required reading - for anyone planning on using conditional compilation in production code
Conditional compilation scripts on LiveSQL
Tim Hall (Oracle-BASE) coverage of conditional compilation
Conditional compilation documentation
My Oracle Magazine article on this topic
Conditional Compilation Series
1. An introduction to conditional compilation
2. Viewing conditionally compiled code: what will be run?
3. Writing code to support multiple versions of Oracle Database
4. Setting and using your own conditional compilation flags
5. How to make sure your code FAILS to compile