Skip to main content

An introduction to conditional compilation

1st in a series on conditional compilation. See end of post for links to all posts in the series.

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.
You implement conditional compilation by placing compiler directives (commands) in your source code.

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:

Selection directives

Use the $IF directive to evaluate expressions and determine which code should be included or avoided.

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 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:
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;
/
Next up: use my own application-specific inquiry directive, along with one provided by Oracle:
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;
/
Finally, I use $ERROR to force a compilation error if anyone tries to compile this code on a version earlier than 12.1.
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;
/

Resources
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




Comments

  1. Steven,

    Regarding conditional compilation, I'm really struggling with the question why on earth the ver_le_* constants have been introduced.
    Maybe you could shed some light on when they are useful.

    Spoiler alert: I never use them, I always use the constants 'version' and 'release'.

    Consider the (rather useless) function:
    create or replace function test_function
    return varchar2
    is
    functionresult varchar2(100);
    begin -- test_function
    functionresult := $if dbms_db_version.ver_le_18
    $then
    'Older version'
    $else
    'Newer version'
    $end
    ;

    return to_char (functionresult);
    end test_function;
    /

    And the use of the function:
    select test_function from dual;

    Now, it may be obvious that what I want is:
    If I'm on a database version higher than 18, return 'Newer version'
    If I'm on a database version less than or equal to 18, return 'Older version'

    Function looks good, let's test it...

    Version 19:
    Function created.
    'Newer version'

    Version 18:
    Function created.
    'Older version'

    ...Man, I'm good. This function is awesome!...

    Version 12.2:
    Function created with compilation errors.
    show errors
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/37 PL/SQL: Statement ignored
    6/27 PLS-00174: a static boolean expression must be used
    6/43 PLS-00302: component 'VER_LE_18' must be declared

    And that's perfectly reasonable, given that this constant obviously didn't exist until version 18 was released.
    So, the question remains what the use is of a constant "Is this version LESS THAN OR equal to x" if it prevents your code from being compiled on versions LESS THAN x.

    I'm probably missing something, because I consider the good plsql people at Oracle smart enough to not introduce useless stuff.
    But I really don't see the usefulness, especially since using 'version' and 'release' always works, albeit with a little extra coding.

    ReplyDelete
  2. Hello Erik, All,

    If you look into the documentation for package DBMS_DB_VERSION,

    https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_DB_VERSION.html#GUID-78469CCD-D866-4E87-9917-C5A87492C6AC

    you will see an example for how to use the "LE" booleans so that to avoid this problem.
    Specifically, if you want to test several ones, then always use them in ascending version+release
    order, starting from the one for your "oldest" database version,
    so that for each database version you will only use the existing constants during compilation,
    even if the pre-compiled code does contain references to later introduced "LE" booleans.

    As by the white paper specified in Steven's post, the reason for using the "LE" constants
    vs the VERSION/RELEASE constants is related to pl/sql unit invalidation,
    because the "LE" constants will not change their values during further upgrades,
    once the current database release becomes higher than the release where each constant
    was introduced, while at least one of the VERSION/RELEASE pair will always change its value
    with each database upgrade, so using these might cause more unit invalidations.

    Thanks a lot & Best Regards,
    Iudith Mentzel

    ReplyDelete
  3. Hmmm?
    Now there's an interesting idea!
    Reading the documentation?
    Do you think that helps?

    Seriously: guilty.
    In this case I didn't read that.
    For some reason I fell into the trap of "hé, I know constants and I know the IF structure. So what there's a dollarsign in front of it?"
    And I didn't even wake up after above mentioned "I don't understand"
    :)

    I corrected that huge mistake, and now it all makes perfect sense.

    Thanks for patiently tutoring this nitwit, Iudith, I needed this gentle RTFM. :)

    ReplyDelete

Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers. In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply. All the code shown in this example may be found in this LiveSQL script . How to Get a Mutating Table Error I need to implement this rule on my employees table: Your new salary cannot be mo

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch,

Working With JSON Arrays in PL/SQL

Oracle Database 12c Release 2 built upon the 12.1 SQL/JSON features by adding a number of builtin object types (similar to classes in object-oriented languages) for manipulating JSON data in PL/SQL blocks. In this post, I explore some of the array-oriented JSON features, all made available through the JSON_ARRAY_T type and its methods. Just like a class, an object type offers a pre-defined constructor function to instantiate new instances of that type, static methods and member methods. Here are the methods you are most likely to use: A couple of things to remember, generally, about working with JSON elements generally and JSON arrays specifically in PL/SQL: Error Handling Behavior By default, if an error occurs when you call a member method for your JSON array (or object), NULL is returned. In other words, an exception is not  raised back to your block. If you want errors to be propagated from the method as an exception, call the ON_ERROR method and pass a value greate