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:
Boolean, PLS_INTEGER, and NULL literals, plus combinations of these literals.
Boolean, PLS_INTEGER, and VARCHAR2 static expressions.
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).
Static constants defined in a PL/SQL package.
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