Conditional compilation in Oracle PL/SQL Page - 3

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.

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.