Working With PL/SQL Code

The $IF Directive

Use the selection directive, implemented through the $IF statement, to direct the conditional compilation step in the preprocessor. Here is the general syntax of this directive:

  $IF Boolean-expression
  $THEN
     code-fragment
  [ $ELSEIF Boolean-expression
  $THEN
     code-fragment]
  [ $ELSE 
     code-fragment]
  $END

where Boolean-expression is a static expression (it can be evaluated at the time of compilation) that evaluates to TRUE, FALSE, or NULL The code-fragment can be any set of PL/SQL statements, which will then be passed to the compiler for compilation, as directed by the expression evaluations.

Static expressions can be constructed from any of the following elements:

1. Boolean, PLS_INTEGER, and NULL literals, plus combinations of these literals.
   
2. Boolean, PLS_INTEGER, and VARCHAR2 static expressions.
   
3. Inquiry directives: identifiers prefixed with $$. These directives can be provided by Oracle (e.g., $$PLSQL_OPTIMIZE_LEVEL; the full list is provided in the earlier section “The Optimizing Compiler”) or set via the PLSQL_CCFLAGS compilation parameter (also explained earlier).
   
4. Static constants defined in a PL/SQL package.
   
5. It can include most comparison operations (>, <, =, <> are fine, but you cannot use an IN expression), logical Boolean operations such as AND and OR, concatenations of static character expressions, and tests for NULL.

A static expression may not contain calls to procedures or functions that require execution; they cannot be evaluated during compilation and therefore will render invalid the expression within the $IF directive. You will get a compile error as follows:

  PLS-00174: a static boolean expression must be used

Here are examples of static expressions in $IF directives:

• If the user-defined inquiry directive controlling debugging is not null, then initialize the debug subsystem:
  
    $IF $$app_debug_level IS NOT NULL $THEN
       debug_pkg.initialize;
    $END 
  
• Check the value of a user-defined package constant along with the optimization level:
  
    $IF $$PLSQL_OPTIMIZE_LEVEL = 2 AND appdef_pkg.long_compilation
    $THEN
       $ERROR 'Do not use optimization level 2 for this program!'
    $END

String literals and concatenations of strings are allowed only in the $ERROR directive; they may not appear in the $IF directive.

The $ERROR Directive

Use the $ERROR directive to cause the current compilation to fail and return the error message provided. The syntax of this directive is:

  $ERROR VARCHAR2-expression $END

Suppose that I need to set the optimization level for a particular program unit to 1, so that compilation time will be improved. In the following example, I use the $$ inquiry directive to check the value of the optimization level from the compilation environment. I then raise an error with the $ERROR directive as necessary.

  /* File on web: cc_opt_level_check.sql */
  SQL> CREATE OR REPLACE PROCEDURE long_compilation
   2  IS
   3  BEGIN
   4  $IF $$plsql_optimize_level != 1
   5  $THEN
   6     $error 'This program must be compiled with optimization level = 1' $end
   7  $END
   8     NULL;
   9  END long_compilation;
  10  /

Warning: Procedure created with compilation errors. 

  SQL> SHOW ERRORS
  Errors for PROCEDURE LONG_COMPILATION:

  LINE/COL ERROR
  -------- ---------------------------------  6/4      PLS-00179: $ERROR: This program
           must be compiled with
           optimization level = 1