Conditional compilation was a feature added to Oracle 10gR2 for PL/SQL, but appears to be available in releases 9.2 and 10.1. It seems like a cool feature but I had never needed it for anything that I could think of. However as I’ve spent more and more time writing PL/SQL lately I’ve realized that it is an excellent way of protecting instrumentation that may be dependent on external calls. My example involves the Hotsos Instrumentation Library for Oracle (ILO) which I have been using a lot lately. Without conditional compilation, code written that calls the ILO will fail to compile if the ILO hasn’t been installed. However with conditional compilation the code can be “hidden” and can be used without change even if the ILO has not been installed. The following will show an example. I’ve taken the liberty of renaming the HOTSOS_ILO_TASK call to HOTSOS1_ILO_TASK simply because the package is installed in my test database.

My first example attempts to compile my procedure after setting my selection directive (i.e. hotsos_ilo) to true. The code will fail to compile due to the lack of the HOTSOS1_ILO_TASK package.

SQL> alter session set plsql_ccflags = 'hotsos_ilo:true';

Session altered.

SQL> create or replace procedure test_cond
2 is
3 begin
4 $IF $$hotsos_ilo $THEN
5 HOTSOS1_ILO_TASK.BEGIN_TASK(
6 module => 'Set Module',
7 action => 'Set Action');
8 $END
9 --
10 dbms_lock.sleep(5);
11 --
12 $IF $$hotsos_ilo $THEN
13 HOTSOS1_ILO_TASK.END_TASK;
14 $END
15 end;
16 /


Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST_COND:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/5 PL/SQL: Statement ignored
5/5 PLS-00201: identifier 'HOTSOS1_ILO_TASK.BEGIN_TASK' must be
declared
13/5 PL/SQL: Statement ignored
13/5 PLS-00201: identifier 'HOTSOS1_ILO_TASK.END_TASK' must be
declared


The next example shows a successful compilation after setting the selection directive (i.e. hotsos_ilo) to false.

SQL> alter session set plsql_ccflags = 'hotsos_ilo:false';

Session altered.

SQL> --
SQL> create or replace procedure test_cond
2 is
3 begin
4 $IF $$hotsos_ilo $THEN
5 HOTSOS1_ILO_TASK.BEGIN_TASK(
6 module => 'Set Module',
7 action => 'Set Action');
8 $END
9 --
10 dbms_lock.sleep(5);
11 --
12 $IF $$hotsos_ilo $THEN
13 HOTSOS1_ILO_TASK.END_TASK;
14 $END
15 end;
16 /


Procedure created.

SQL>

This entry was posted on Sunday, October 5th, 2008 at 1:57 am and is filed under Oracle DBA. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.