f you’re an Oracle PL/SQL developer, you have two familiar needs:

1. Include as much debugging code as possible without risking a performance hit when the code is running in production
2. Create code that takes advantage of the newest features in the latest Oracle Database versions but still runs on older versions?without creating “bloatware” that stuffs the shared pool with separate code for each database version

Developers working in other languages have long had an easy solution that addresses both these needs: the preprocessor or precompiler. For example, the C++ preprocessor can handle conditional compilation directives and string substitution, which allow developers to compile different subsets of the source code for development, production, different DB versions, and so on. But Oracle PL/SQL has been missing this capability?until now.

The recently released Oracle Database10gR2 has a new feature that makes it easy to meet these needs: PL/SQL conditional compilation (CC). PL/SQL CC is not technically a precompiler or preprocessor; it’s functionality that Oracle added to the earliest stages of the existing PL/SQL compilation process. The same engine carries it out along with the other stages. However, PL/SQL is sometimes referred to as a preprocessor, as in the name of the DBMS_PREPROCESSOR package (more on that later).

This article shows you how to use Oracle 10gR2’s new CC feature, how to view the results of conditional compilation with DBMS_PREPROCESSOR, how to leverage this new feature with DBMS_DB_VERSION to create version-independent code, and how to use conditional compilation to create a lightweight debugging framework.

Using PL/SQL Conditional Compilation

PL/SQL conditional compilation is very easy to use. You need to learn just two new types of semantic construct:

1. Compiler directives
2. Conditional compilation flags (ccflags), which for the conditional compiler are like “constants” that can be set at a system, session, or individual compilation level (Some default flags are available, and you can supply your own.)

The most basic principle of conditional compilation is that you must be able to compile your code conditionally (i.e., compile certain code IF certain conditions are met, ELSE compile other code). So, the most basic building block for conditional compilation is a compiler directive that works like an if… then… else construct.

In PL/SQL CC, this building block is known as the selection directive. It works very similarly to the if… then… else construct in PL/SQL. The syntax is:

$IF condition $THEN .....[$ELSIF condition $THEN.... ][$ELSE .....]$END    

As you may have guessed, all PL/SQL CC tokens are preceded by a dollar sign ($).

The two key differences between the CC selection directive and the standard PL/SQL if… then… else construct are the following:

1. The CC selection directive terminates with $END, not $END $IF.
2. The condition in the CC IF-test has significant restrictions. Since the compiler needs to evaluate condition at compile-time, condition cannot be variable?it must be a constant value at compile-time. Specifically, it must be a static Boolean expression. A good approximation to this concept is an expression that is static at compile-time (see Sidebar 1. Static Boolean Expressions for a more in-depth explanation).

So, for example, this is acceptable:

$IF -4 = 8 $THEN... $END

But this is not, since ?4=TRUNC(sysdate,'MM') is not a static Boolean expression:

$IF -4=TRUNC(sysdate,'MM') $THEN... $END

The IF-test expressions can include package constants (set elsewhere, not in the same package, of course) so long as these are static Boolean expressions themselves, for example:

create or replace package PayrollApp_CC_Constants is	c_SpecialNumber constant PLS_INTEGER := 8;	c_DebugOn constant BOOLEAN := false;end PayrollApp_CC_Constants;....$IF PayrollApp_CC_Constants.c_DebugOn $THEN	debug('Debug On');$ELSIF PayrollApp_CC_Constants.c_SpecialNumber = -4 $THEN	raise;$END....

(Author’s Note: In this and all subsequent examples, debug('X') does not refer to any PL/SQL language element. If not otherwise specified, it refers to a hypothetical procedure, found elsewhere in the hypothetical package being used in the example, which takes a debug message as argument and presumably prints it out or logs it somewhere.)

When using static-expression constants in your IF-test expressions for conditional compilation, it’s best to separate these constants out and keep them all together in a package that contains nothing but such constants. This way, it’s easier to see at a glance what the values of the constants determining your compilation results are. They are also less likely to be confused with regular application constants in the PL/SQL code, which could, for example, lead to such a constant accidentally being set to a non-static value or expression. Finally, separating out the constants means you are guaranteed not to tangle yourself in circular compilation dependencies.

The following example makes it very clear that the conditional compilation selection directives are processed before Oracle tries to compile, or even parse, the rest of the code. Consider this simple procedure:

create or replace procedure testproc isbegin$if TRUE $then  dbms_output.put_line('We got here');$else  RANDOM BAD STUFF, NOT EVEN SYNTACTICALLY CORRECT$endnull;end testproc;

The text of this procedure contains big syntax mistakes (“RANDOM BAD STUFF, NOT EVEN SYNTACTICALLY CORRECT“), but these mistakes are in a branch of the $IF - $THEN - $ELSE that will never be evaluated. And this procedure compiles just fine:

SQL> create or replace procedure testproc is  2  begin  3  $if TRUE $then  4     dbms_output.put_line('We got here');  5  $else  6     RANDOM BAD STUFF, NOT EVEN SYNTACTICALLY CORRECT  7  $end  8  null;  9  end testproc;10 /Procedure created.SQL> show errors procedure testprocNo errors.

Using Conditional Compilation Flags

In the conditional compilation IF-test, and elsewhere in the code, you can also use static expressions that are passed directly to the compiler at compile-time, not set in any PL/SQL code. Three options are available:

• PL/SQL Compiler initialization parameters, such as NLS_Length_Semantics
• User-defined conditional compilation flags, or ccflags
• Oracle’s two predefined ccflags, PLSQL_Unit and PLSQL_Line

You reference these non-PL/SQL expressions using the inquiry directive, or $$. That is, you preface a ccflag or a PL/SQL Compiler initialization parameter with $$. This signals the conditional compilation engine to reads its value in at compile-time, for example:

$IF $$PLSQL_LINE=15 $THEN	debug('That was line 15');$END ;

You set user-defined ccflags by assigning a string of name-value pairs to the Oracle parameter PLSQL_CCFLAGS in the format ‘name:value[,name:value][,name:value]….‘. For example, to set the values of ccflags named “set_debug_on”, “use_10g_features”, and “compile_w_extras”, you would set PLSQL_CCFLAGS as follows:

PLSQL_CCFLAGS = 'set_debug_on:true,use_10g_features:false,compile_w_extras:false' ;

You can set PLSQL_CCFLAGS at the session or system level with ALTER SESSION or ALTER SYSTEM, respectively. Or, you can set PLSQL_CCFLAGS at compile time, for individual package or procedure compilation.

Here’s an example of setting user-defined ccflags at compile time:

SQL> alter package PAYROLL_CALC compile   2> plsql_ccflags =  3> 'no_commit_trans:true,print_debug_msg:true'  4> reuse settings ;

Here’s an example of setting the value of PLSQL_CCFLAGS at the ALTER SESSION level:

SQL> alter session set PLSQL_CCFLAGS =   2> 'set_debug_on:true' ;

And here’s an example of setting the value of PLSQL_CCFLAGS at compile-time:

SQL> alter system set PLSQL_CCFLAGS =   2> 'use_10g_features:false' ;

You can combine package constants with ccflags in the conditional compilation IF-test, for example:

$IF $$DEBUG_ON or PayrollConstants.c_Debug $THEN	debug('Error');$END

You can set the package constant to TRUE during development and FALSE thereafter. After development, you can recompile the package with the ccflag DEBUG_ON set to TRUE whenever it’s necessary for debugging. The following case study looks at this concept in more detail.

Case Study: Lightweight Debug Framework

In the following example of a lightweight debugging framework, a PL/SQL constant, set in a dedicated package, controls whether or not the code is compiled with debugging features. If you set this constant to false, you can override it by setting a separate ccflag to true:

create or replace package DebugConstants is	c_debug constant boolean := true;end;/create or replace package debugpkg is	procedure debug (p_msg in varchar2, p_lineNum in number, p_plsUnit in varchar2);end debugpkg;/create or replace package debugpkg is	procedure debug (p_msg in varchar2, p_lineNum in number, p_plsUnit in varchar2) is		v_error_string varchar2(32767);	begin		$IF $$DEBUG_ON or DebugConstants.c_debug		$THEN			v_error_string := 'ERROR in ' || p_plsUnit || ', at line ' || p_lineNum || ': ' || p_msg;			dbms_output.put_line(substr(v_error_string,1,255));		$ELSE			null;		$END	end debug;end debugpkg;/

As you can see, if DebugConstants.c_debug is false and the ccflag debug_on is false, then a call to DebugPkg.debug is simply a call to null, which is very lightweight. Let’s create a simple package that uses this debugging framework:

create or replace package testpkg is	procedure run;end;/create or replace package body testpkg is	procedure run is		v_dummy varchar2(1);		begin		dbms_output.put_line('Beginning test run...');		select * into v_dummy from dual;		if v_dummy = 'X' then			debug('Dummy is X!', $$plsql_line, $$plsql_unit);		end if;	end run;end testpkg;

Note that the call to DebugPkg.debug uses the new Oracle-defined ccflags, $$plsql_line and $$plsql_unit. With the value of DebugConstants.c_debug set to true, a call to debug prints out the debug message with the associated unit and line:

SQL> set serverout onSQL> exec testpkg.run ;Beginning test run...ERROR in TESTPKG, at line 19: Dummy is X!PL/SQL procedure successfully completed.

Now change the stored constant c_debug:

create or replace package DebugConstants is    c_debug constant boolean := false;end;

If you run the test procedure after setting the stored constant c_debug to FALSE, no debug information is printed out:

SQL> exec testPkg.run ;Beginning test run...PL/SQL procedure successfully completed.

The debug info is no longer printed out because when the package DebugConstants was recompiled, Oracle remembered the dependency and recompiled DebugPkg without the debugging code.

The Error Directive

PL/SQL conditional compilation includes only three directives. You’ve already seen two of them, the selection directive ($IF... $END) and the inquiry directive ($$). The remaining directive is the error directive, a syntactical element that offers even more help for the developer in need. You can keep unfinished code from compiling and remind yourself what needs to be finished by making the compiler raise an error on incomplete sections.

The syntax of the error directive is very simple:

$ERROR varchar2_string $END

When you compile code that includes an error directive, the PL/SQL compiler raises a PLS-00179 error on your $ERROR line and shows the value of varchar2_string as the error text; for example:

create or replace package testErrDirective is  procedure run;end;/create or replace package body testErrDirective is  procedure run is  begin    dbms_output.put_line('Beginning test run...');    $ERROR      'Oh No! There''s no code at line ' ||      $$plsql_line      || ' in ' || $$plsql_unit    $END  end run;end testpkg;/

The package body will compile with errors, and the error text will show as the message in the error directive:

SQL> show errorsErrors for PACKAGE BODY TESTERRDIRECTIVE:LINE/COL ERROR-------- ------------------------------------------5/3      PLS-00179: $ERROR: Oh No! There's no code         at line 6 in TESTERRDIRECTIVE

If, like me, you’ve paged through old code with your eyebrows slowly creeping toward your hairline as you pass comment after comment that says something like:

--Joe: this doesn't work ; fix before putting into prod!!! 

Then you’ll immediately recognize the utility of the error directive.

Version-Dependent Conditional Compilation

The introduction mentioned that developers often need to be able to compile different code depending on the target database version in order to take advantage of the latest Oracle RDBMS features. To make this even easier to do with PL/SQL conditional compilation, Oracle has accompanied its 10gR2 release with a new PL/SQL-supplied package, DBMS_DB_VERSION. This package is a simple set of constants that hold the version and release numbers. Code that uses DBMS_DB_VERSION might look like this:

...$IF DBMS_DB_VERSION.VERSION=10 $THEN... [version 10 code] ...$ELSE ... [code for earlier versions]...$END...

At compile time, only one of these two branches will be compiled into the package, saving on memory and overhead.

For detailed information on the constants in the DBMS_DB_VERSION package, see the PL/SQL Supplied Packages chapter.

Conditional Compilation on Pre-10gR2 Databases

You might be wondering what good version-dependent conditional compilation is, when PL/SQL conditional compilation is a new 10gR2 feature. The answer is that Oracle has taken the very unusual step of backporting this new feature to earlier releases. PL/SQL conditional compilation is available for 9iR2 in patchsets from 9.2.0.6 onwards, and for 10gR1 in patchsets from 10.1.0.4 onwards. In 9.2.0.6, conditional compilation is disabled by default but can be enabled by setting an underscore (hidden) parameter; in 10.1.0.4, the feature is available by default but can be disabled via the same underscore parameter. (Note that Oracle only supports changing the value of underscore parameters under the direct guidance of Oracle Support.) In 10.2, CC cannot be disabled and the underscore parameter is obsolete.

Viewing CC Results with DBMS_PREPROCESSOR

When your code includes conditional compilation directives, the code in DBA_SOURCE is no longer an exact picture of the compiled code in memory. The text in DBA_SOURCE includes the compiler directives and all the branches of the source. Fortunately, Oracle provides another new supplied package, DBMS_PREPROCESSOR, which lets you see what your code looks like post-compilation.

As a brief example, consider what happens if you execute DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE on the DebugPkg code from the previous lightweight debugging framework. (Recall that you left the stored constant DebugConstants.c_debug set to FALSE.)

First, compile DebugPkg with the debugging code in by setting the ccflag debug_on to TRUE:

SQL> alter package debugpkg compile plsql_ccflags = 'debug_on:true' reuse settings ;Package altered.

Now, run DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE to see what the compiled code looks like:

SQL> begin   2> dbms_preprocessor.print_post_processed_source3>	('PACKAGE BODY','BULKLOAD','DEBUGPKG');4>	end;5>	/package body debugpkg is  procedure debug (p_msg in varchar2, p_lineNum in number, p_plsUnit in varchar2) is    v_error_string varchar2(32767);  begin    v_error_string := 'ERROR in ' || p_plsUnit || ', at line ' || p_lineNum || ': ' || p_msg;                     dbms_output.put_line(substr(v_error_string,1,255));  end debug;end debugpkg;PL/SQL procedure successfully completed.

As you can see, the selection directives $IF...$END are absent from the compiled source, and only the code branch with the debug code is included. If you recompile DebugPkg with the ccflag debug_on set to FALSE, you see the other branch instead:

SQL> alter package debugpkg compile plsql_ccflags = 'debug_on:false' reuse settings ;Package altered.SQL> begin   2> dbms_preprocessor.print_post_processed_source6>	('PACKAGE BODY','BULKLOAD','DEBUGPKG');7>	end;8>	/package body debugpkg is  procedure debug (p_msg in varchar2, p_lineNum in number, p_plsUnit in varchar2) is    v_error_string varchar2(32767);  begin    null;  end debug;end debugpkg;PL/SQL procedure successfully completed.

Note the following factors when using DBMS_PREPROCESSOR:

• The stored procedure passed does not need to be valid.
• You can also pass in an anonymous PL/SQL block, or a PL/SQL table of source lines.
• An associated function, GET_POST_PROCESSED_SOURCE, returns post-processed source code as a PL/SQL table of source lines, instead of printing it out with DBMS_OUTPUT.PUT_LINE.

If you run DBMS_PREPROCESSOR on code that contains an error directive, the results include an error at the appropriate place:

SQL> begin   2> dbms_preprocessor.print_post_processed_source9>	('PACKAGE BODY','BULKLOAD',10>	'TESTERRDIRECTIVE');11>	end;12>	/package body testErrDirective is  procedure run is  begin    dbms_output.put_line('Beginning test run...');ORA-06550: line 5, column 3:PLS-00179: $ERROR: Oh No! There's no code at line 6 inPL/SQL procedure successfully completed.

Smaller, Faster Run-Time Code Is in Your Future

Conditional compilation is as versatile as it is simple to use. The few CC uses covered here really only scratch the surface. For example, you could customize master code for different clients; you could include separately licensed features in the main code body and simply hide them behind a set-to-false ccflag for customers that haven’t licensed them; and so on.

At minimum, every developer who’s concerned about the cumulative performance impact of a million “if v_debug=true then... else...” calls can now use conditional compilation to scrub these calls completely from production code, while leaving the debug framework in place for future debugging.

I encourage you to start using this versatile new feature today to reap the benefits of smaller, faster run-time code.