Table of ContentsConditional compilation in Oracle PL/SQL
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 
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.
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.
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.
Include a compilation flag TESTING in the session and recompile the function P_GET_SAL using the session compilation flags.
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.
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.
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.
Demonstrating error directive: An error number can be included under compilation flags using ALTER SESSION command as below.
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.
Now we execute the function in a SELECT statement.
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.
Oracle 11g Tutorial