Oracle 10g introduced conditional compilation feature in its second release. The conditional compilation feature enables a developer to impose conditions on the compiler. This implies that out of the complete source code of a program unit, only the part of code, which qualifies these conditions, would be compiled.

The compiler conditions can exist in any of the three sections (DECLARE, BEGIN, EXCEPTION) of a PL/SQ block. The nature of this condition is different from the one which is involved in the implementation of any business logic. Unlike the IF THEN ELSE construct, the conditional compilation accesses static constants to extract the part of code for compilation. Instead of using non persistent memory variables in a program unit, they use directives to read the database parameters like db version and program compilation flags.

During object compilation process, compiler conditions would be executed and only the part of the code which complies with the conditions, would participate in compilation process. Rest of the code would remain in non compiled state in unsupported versions of Oracle.

Let’s take up a genuine instance; a support team maintains a 10g production and 10g UAT database server for a retail application. On stakeholder’s request, production database has been upgraded from Oracle 11g. For every new functionality request or application development, support team professionals would design new program units. If the program unit uses version specific feature, developers need to create separate program for testing and production servers. Another option is to create separate copies of same unit with non applicable code commented in each script. Conditional compilation comes as a hopeful activity in such scenarios.

1. Conditional compilation benefits

Using conditional compilation feature of Oracle database, a program can be written using the version specific features; thus, taking full advantage of enhancements and additions applicable in that database version. This makes the program independent of the database environment used to run it.

Now, the same code can easily fit into the upgraded production database and older version of testing (UAT) environment. It can be successfully compiled and used in the application mounted on different database versions. One more positive aspect of the feature comes out during database migration. The code can stand stiff and live irrespective of target database.

The feature also lowers down the maintenance and debugging overhead of the program units.

2. Compiler Directives

As stated earlier, conditional compilation use directives to access the database and object level compilation parameters. There are three types of directives namely, Selection, Inquiry and Error directives. Besides this directive, Oracle provides certain utility packages for better support and usage of the feature.

2.1. Selection directive

The directive is analogous to the IF THEN ELSE construct of PL/SQL. The dollar sign ($) as the prefix serve as the differentiation factor. It can be used at any of the sections of a program unit. If the specified conditions by $IF selection directive are satisfied, the corresponding set of action statements would be evaluated for compilation; else control would move to next $ELSE directive section.

The compilation conditions can use inquiry directives (covered later), object level constants and flags, and database version parameters.

Example Syntax [1]

$IF $$[CONDITIONS] $THEN
…
$ELSE
…
$END

2.2. Inquiry directive

Inquiry directive ($$) is used to access value of the object level compilation flags. These flags are set in KEY-VALUE string format under PLSQL_CCFLAGS parameter.

Compilation flags can be set at system or session level using ALTER [SYSTEM | SESSION] command. The object level compilation settings are synchronized with the session in which they are compiled. Also these flags are initialized with the current session settings every time they are compiled; unless they are altered using REUSE SETTINGS clause. Let’s take a look at this scenario with a small example.

Example Syntax [2]

$IF $$[Compilation Flag] $THEN
…
$END

2.3. Error directive

The error directive $ERROR $END handles the error situation in compilation conditions. If the program doesn’t fits for compilation in any of the database environments, its creation can be restricted by raising compilation errors. The program would be created in INVALID state and the compilation error would be held up with USER_ERRORS dictionary view, until it is resolved. It can be displayed using SHOW ERRORS command in SQL. Oracle associates a generic message number PLS-00179 with the compilation errors.

Example Syntax [3]

$ERROR varchar2_static_expression $END

3. DBMS_DB_VERSION

It is an Oracle supplied package, introduced in Oracle 10g Release 2, to hold the persistent and static constants to denote the database versions and its release number. The constants can be referenced externally to get information about the current version, current release, and applicable database version. For demonstration, See the table below shows the values of the constants as applicable to Oracle 11g release 2.

4. Illustration of Conditional compilation

We take up the previous scenario where a support team maintains a testing database in 10g version and development database in 11g version. A new customization requires a new procedure creation.

Result caching feature was introduced in Oracle 11g release. To make use of the feature, programmers design a single procedure and impose conditions upon the compiler to evaluate selected code applicable for their database version.

A function P_GET_SAL has been created which returns the salary of an employee. For 11g environment, it must declare a cursor with result cache feature. On the other hand, for 10g environment, the cursor should be a normal one. Check the compilation conditions in the below function code. It uses DBMS_DB_VERSION constant to get the database version.

CREATE OR REPLACE FUNCTION P_GET_SAL (P_EMPID NUMBER)
RETURN NUMBER
IS
L_SAL NUMBER;
$IF DBMS_DB_VERSION.VERSION > 11 $THEN
  CURSOR C IS
  SELECT /*+ RESULT_CACHE */ SALARY
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPID;
$ELSE
  CURSOR C IS
  SELECT SALARY
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPID;
$END
BEGIN
   OPEN C;
   FETCH C INTO L_SAL;
   CLOSE C;
   RETURN L_SAL;
END;
/
FUNCTION created.

Testing the object level compilation parameters – Object level compilation flags can be set to read important information about the object. They are used by the inquiry directives within the compilation conditions.

Before that, you will need to check current setting of compilation flags.

SQL> SELECT PLSQL_CCFLAGS FROM USER_PLSQL_OBJECT_SETTINGS
WHERE NAME = ‘P_GET_SAL’;
 
PLSQL_CCFLAGS
-----------------------

Include a compilation flag TESTING in the session and recompile the function P_GET_SAL using the session compilation flags.

SQL> ALTER SESSION SET PLSQL_CCFLAGS = ‘TESTING:TRUE’;

From now onwards, all the objects created in the current session would inherit the same compilation flags. I shall create a function F_GETSAL_USING_COMP_PARAM, which uses the TESTING flag. Note the usage of inquiry directive.

SQL> CREATE FUNCTION F_GETSAL_USING_COMP_PARAM(P_EMPID NUMBER)
  2  RETURN NUMBER
  3  IS
  4  L_SAL NUMBER;
  5  $IF $$TESTING $THEN
  6    CURSOR C IS
  7    SELECT /*+ RESULT_CACHE */ SALARY
  8    FROM EMPLOYEES
  9    WHERE EMPLOYEE_ID = P_EMPID;
 10  $ELSE
 11    CURSOR C IS
 12    SELECT SALARY
 13    FROM EMPLOYEES
 14    WHERE EMPLOYEE_ID = P_EMPID;
 15  $END
 16  BEGIN
 17     OPEN C;
 18     FETCH C INTO L_SAL;
 19     CLOSE C;
 20     RETURN L_SAL;
 21* END;
 
FUNCTION created.

Inquiry directives can even make use of static packaged variables while defining the compilation conditions. I shall create a package PKG_COND_COMPILE to declare the statics constants.

CREATE OR REPLACE PACKAGE PKG_COND_COMPILE IS
 $IF DBMS_DB_VERSION.VERSION >= 11 $THEN
    PARAM_TESTING CONSTANT BOOLEAN := TRUE;
 $ELSE
    PARAM_TESTING CONSTANT BOOLEAN := FALSE;
 $END
END;

Note that the packaged variable must be a static constant, else it would raise exception ‘PLS-00174: a static boolean expression must be used’. Refer the below function which uses the package variables.

CREATE OR REPLACE FUNCTION P_GET_SAL (P_EMPID NUMBER)
RETURN NUMBER
IS
L_SAL NUMBER;
$IF PKG_COND_COMPILE.PARAM_TESTING $THEN
  CURSOR C IS
  SELECT /*+ RESULT_CACHE */ SALARY
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPID;
$ELSE
  CURSOR C IS
  SELECT SALARY
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = P_EMPID;
$END
BEGIN
   OPEN C;
   FETCH C INTO L_SAL;
   CLOSE C;
   RETURN L_SAL;
END;
/
 
FUNCTION created.

Demonstrating error directive: An error number can be included under compilation flags using ALTER SESSION command as below.

SQL> ALTER SESSION SET PLSQL_CCFLAGS = 'TESTING:TRUE,DEV:FALSE,ERR:83949'; 
SESSION altered.

The function below F_CHECK_ERR checks the compilation flag TESTING to return 1 when it is true. If its creation has to be restricted in all other databases, it must raise compilation error upon execution. Check the function below.

CREATE OR REPLACE FUNCTION F_CHECK_ERR
RETURN NUMBER
IS
BEGIN
   $IF $$TESTING $THEN
	   RETURN 1;
   $ELSE
	   $ERROR 'This is a Development database:'||$$ERR $END
	   RETURN 0;
   $END
END;
/
 
FUNCTION created.

Now we execute the function in a SELECT statement.

SQL> SELECT F_CHECK_ERR FROM DUAL;
 
F_CHECK_ERR
-----------
          1

Now, Let’s modify the value of TESTING flag from TRUE to FALSE and recompile the above function to synchronize with the changes. Check, what happens upon compilation.

SQL> ALTER SESSION SET PLSQL_CCFLAGS = 'TESTING:FALSE,DEV:TRUE,ERR:83949';
 
SESSION altered.
 
SQL> ALTER FUNCTION F_CHECK_ERR COMPILE;
 
Warning: FUNCTION altered WITH compilation errors.
 
SQL> SHOW ERR
Errors FOR FUNCTION F_CHECK_ERR:
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/5      PLS-00179: $ERROR: This IS a Development DATABASE:83949

5. DBMS_PREPROCESSOR

It is an Oracle supplied package which enables to view the program source code as compiled by the compiler. It displays the actual portion of the code participating in the compilation process, after evaluation of all compilation conditions.

It contains two overloaded subprograms GET_POST_PROCESSED_SOURCE and PRINT_POST_PROCESSED_SOURCE. The structure of the overloaded subprograms is shown in the below snapshot.

For demonstration, Let’s use procedure P_COND_COMPILE. This procedure declares a variable based on the database version, in which the procedure is executed. In Oracle9i and earlier version, it would be number. In 10g version, it would be BINARY_DOUBLE. Oracle 11g compiler would take the variable in SIMPLE_INTEGER format. Again, based on the database version, different values are assigned for different versions.

CREATE OR REPLACE PROCEDURE P_COND_COMPILE
IS
   $IF DBMS_DB_VERSION.VERSION >= 11 $THEN
	M_VERSION_PARAM SIMPLE_INTEGER := 0;
   $ELSIF DBMS_DB_VERSION.VERSION BETWEEN 10 AND 11  $THEN
	M_VERSION_PARAM BINARY_DOUBLE := 0;
   $ELSE
	M_VERSION_PARAM NUMBER;
   $END
BEGIN
   $IF DBMS_DB_VERSION.VERSION >= 11 $THEN
	M_VERSION_PARAM := 739202874;
   $ELSIF DBMS_DB_VERSION.VERSION BETWEEN 10 AND 11 $THEN
	M_VERSION_PARAM := 982.12247293721082739017372819102722191D;
   $ELSE
	M_VERSION_PARAM := 824.12247293721082739017372819102722191;
   $END
   DBMS_OUTPUT.PUT_LINE(M_VERSION_PARAM);
END;

Now, we can print the actual procedure source code as evaluated and compiled by the compiler using DBMS_PREPROCESSOR package.

The snapshot below is the compiled in Oracle 11g database. Note that the non applicable code of the procedure, thus redundant, has been ignored.

The snapshot below shows the code as compiled in Oracle 10g database. Note the changes in the variable declaration and value assignment.

The conditional compilation feature has surely eased the way for migration projects. For a usual patch release in a project or code testing, conditional compilation is a feature to be hunted.