Streamline Your Oracle Production Code with Conditional Compilation : Page 5

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_source
3>	('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_source
6>	('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_source
9>	('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 in

PL/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.

Previous Page