Conditional Compilation for Oracle Database 10g

In this fifth part of a nine-part series on PL/SQL, you’ll learn how conditional compilation lets you compile selected parts of a program when you’re working with Oracle Database 10g. It is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O’Reilly; ISBN: 0596009771). Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

Conditional Compilation

Introduced in Oracle Database 10g Release 2, conditional compilation allows the compiler to compile selected parts of a program based on conditions you provide with the $IF directive.

Conditional compilation will come in very handy when you need to:

  1. Write a program that will run under different versions of Oracle, taking advantage of features specific to those versions. More specifically, you want to take advantage of new features of Oracle where available, but you also need that program to compile and run in older versions. Without conditional compilation, you would have to maintain multiple files or use complex SQL*Plus substitution variable logic.
  2. Run certain code during testing and debugging, but then omit that code from the production code. Prior to conditional compilation, you would need to either comment out lines of code or add some overhead to the processing of your application—even in production.
  3. Install/compile different elements of your application based on user requirements, such as the components for which a user is licensed. Conditional compilation greatly simplifies the maintenance of a code base with this complexity.

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.

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.

First we’ll look at some simple examples, then delve more deeply into the capabilities of each directive. We’ll also learn how to use two packages related to conditional compilation, DBMS_DB_VERSION and DBMS_PREPROCESSOR.

{mospagebreak title=Examples of Conditional Compilation}

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;
  /

{mospagebreak title=The Inquiry Directive}

An inquiry directive is a directive that makes an inquiry of the compilation environment. Of course, that doesn’t really tell you much. So let’s take a closer look at the syntax for inquiry directives and the different sources of information available through the inquiry directive.

The syntax for an inquiry directive is as follows:

  $$identifie r

where identifier is a valid PL/SQL identifier that can represent any of the following:

  1. Compilation environment settings: the values found in the USER_PLSQL_OBJECT_SETTINGS data dictionary view
  2. Your own custom-named directive, defined with the ALTER…SET PLSQL_CCFLAGS command, described in a later section
  3. Implicitly defined directives: $$PLSQL_LINE and $$PLSQL_UNIT, providing you with the line number and program name

Inquiry directives are designed for use within conditional compilation clauses, but they can also be used in other places in your PL/SQL code. For example, I can dis play the current line number in my program with this code:

  DBMS_OUTPUT.PUT_LINE ($$PLSQL_LINE);

I can also use inquiry directives to define and apply application-wide constants in my code. Suppose, for example, that the maximum number of years of data supported in my application is 100. Rather than hardcode this value in my code, I could do the following:

  ALTER SESSION SET PLSQL_CCFLAGS = ‘max_years:100′;

  CREATE OR REPLACE PROCEDURE work_with_data (num_years_in IN PLS_INTEGER )
  IS
  BEGIN
    
IF num_years_in > $$max_years THEN …
  END work_with_data;

Even more valuable, I can use inquiry directives in places in my code where a vari able is not allowed. Here are two examples:

  DECLAR E
     l_big_string VARCHAR2($$MAX_VARCHAR2_SIZE);

     l_default_app_err EXCEPTION;
    
PRAGMA EXCEPTION_INIT (l_default_app_err, $$DEF_APP_ERR_CODE);
  BEGIN

The DBMS_DB_VERSION package

The DBMS_DB_VERSION built-in package offers a set of constants that give you absolute and relative information about the version of your installed database. The constants defined in the Oracle Database 10g Release 2 version of this package are shown in Table 20-2.

Table 20-2. DBMS_DB _VERSION constants

Name of packaged constant

Significance

Value in Oracle Database10g Release 2

DBMS_DB_VERSION.VERSION

 The RDBMS version number, as in 10 for Oracle Database 10g

10

Table 20-2. DBMS_DB _VERSION constants (continued)

Name of packaged constant

Significance

Value in Oracle Database 10g Release 2

DBMS_DB_VERSION.RELEASE

The RDBMS release number, as in 2 for Oracle Data-base 10gRelease 2

2

DBMS_DB_VERSION.VER_LE_9

TRUE if the current version is less than or equal to Oracle9iDatabase

FALSE

DBMS_DB_VERSION.VER_LE_9_1

TRUE if the current version is less than or equal to Oracle9iDatabase Release 1

FALSE

DBMS_DB_VERSION.VER_LE_9_2

TRUE if the current version is less than or equal to Oracle9iDatabase Release 2

FALSE

DBMS_DB_VERSION.VER_LE_10

TRUE if the current version is less than or equal to Oracle Database 10g

TRUE

DBMS_DB_VERSION.VER_LE_10_1

TRUE if the current version is less than or equal to Oracle Database 10g Release 1

FALSE

DBMS_DB_VERSION.VER_LE_10_2

TRUE if the current version is less than or equal to Oracle Database 10gRelease 2

TRUE

While this package was designed for use with conditional compilation, you can, of course, use it for your own purposes.

With each new release of the database, Oracle will add additional constants and will update the values returned by the VERSION and RELEASE constants.

Interestingly, you can write expressions that include references to as-yet undefined constants in the DBMS_DB_VERSION package. As long as they are not evaluated, as in the case below, they will not cause any errors. Here is an example:

  $IF DBMS_DB_VERSION.VER_LE_10_ 2
  $THEN
    
Use this code.
  $ELSEIF DBMS_DB_VERSION.VER_LE_11
    
This is a placeholder for future.
  $ENDIF

{mospagebreak title=Setting compilation environment parameters}

The following information (corresponding to the values in the USER_PLSQL_OBJECT_SETTINGS data dictionary view) is available via inquiry directives:

$$PLSQL_DEBUG
  
Debug setting for this compilation unit

$$PLSQL_OPTIMIZE_LEVEL
   Optimization level for this compilation unit

$$PLSQL_CODE_TYPE
  
Compilation mode for the unit

$$PLSQL_WARNINGS
  
Compilation warnings setting for this compilation unit

$$NLS_LENGTH_SEMANTICS
   Value set for the NLS length semantics

See the cc_plsql_parameters.sql file on the book’s web site for a demonstration that uses each of these parameters.

Referencing unit name and line number

Oracle implicitly defines two very useful inquiry directives for use in $IF and $ERROR directives:

$$PLSQL_UNIT
  
Name of the compilation unit in which the reference
   appears

$$PLSQL_LINE
  
Line number of the compilation unit where the
   reference appears

You can call DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY. FORMAT_ERROR_BACKTRACE to obtain current line numbers, but then you must also parse those strings to find the line number and program unit name. These inquiry directives provide the information more directly. Here is an example:

  BEGIN
    
IF l_balance < 10000
    
THEN
       
raise_error (
          
err_name => ‘BALANCE TOO LOW’
         
,failed_in => $$plsql_unit
         
,failed_on => $$plsql_line
       
);
   
END IF;
   

  END;

Run cc_line_unit.sql to see a demonstration of using these last two directives.

Note that when $$PLSQL_UNIT is referenced inside a package, it will return the name of the package, not the individual procedure or function within the package.

Using the PLSQL_CCFLAGS parameter

Oracle offers a new initialization parameter, PLSQL_CCFLAGS, that you can use with conditional compilation. Essentially, it allows you to define name-value pairs, and the name can then be referenced as an inquiry directive in your conditional compilation logic. Here is an example:

  ALTER SESSION SET PLSQL_CCFLAGS = ‘use_debug:TRUE, trace_level:10′;

The flag name can be set to any valid PL/SQL identifier, including reserved words and keywords (the identifier will be prefixed with $$, so there will be no confusion with normal PL/SQL code). The value assigned to the name must be one of the following: TRUE, FALSE, NULL, or a PLS_INTEGER literal.

The PLSQL_CCFLAGS value will be associated with each program that is then compiled in that session. If you want to keep those settings with the program, then future compilations with the ALTER…COMPILE command should include the REUSE SETTINGS clause.

Because you can change the value of this parameter and then compile selected program units, you can easily define different sets of inquiry directives for different programs.

Note that you can refer to a flag that is not defined in PLSQL_CCFLAGS; this flag will evaluate to NULL. If you enable compile-time warnings, this reference to an undefined flag will cause Oracle to report a PLW-06003: unknown inquiry directive warning (unless the source code is wrapped).

Please check back next week for the continuation of this article.

Google+ Comments

Google+ Comments