Oracle 10gR2 – Conditional PL/SQL Compilation(轉)

For one of our sessions for the Knowledge Center Oracle Server Development I had to prepare a presentation and demonstration on new Oracle 10gR2 features in SQL and PL/SQL – as well as reflect on some by now somewhat more established 10gR1 features. If you are interested in this presentation, you can download it here KC_SERVERDEVELOPMENT_10gR2_R1.ppt (some small parts are in Dutch). Topics included: Crossing Boundaries with DBMS_OUTPUT, DML with Error Log – Constraint Violations allowed, SQL*Plus Autotrace based on DBMS_XPLAN, Conditional PL/SQL Compilation, 10x faster transactions – Asynchronous Commit.

More time was devoted to some 10gR1 functions such as 10g Merge enhancements, Regular Expressions, 10g Flashback improvements, Partition Outer Join and in more detail the SQL Model Clause, Expression Filters and dbms_frequent_itemset datamining (on these three topics, also see the paper Oracle 10g’s Finest – The Top 3 SQL and PL/SQL Features new in 10g.

The most surprising part of the presentation for me was Conditional PL/SQL Compilation. I had read some things on this feature, primarily stuff from Tom Kyte and Steven Feuerstein, and I had a basic understanding of what it can do. But only when I sat down with Steven Feuerstein’s classic PL/SQL Programming, the 4th Edition (September 2005) that covers versions up to Oracle 10gR2, I had a more thorough understanding of what it entails, this conditional compilation. For this session I prepared some slides, a demonstration and a brief workshop. In this post I will share some of my findings – largely Feuerstein’s findings in my own words to be specific! Note is often considered a new feature of Oracle 10g Release 2, but is actually already available in Oracle 10g Release 1 (10.1.0.4.0).

Conditional PL/SQL Compilation

Conditional Compilation allows us to produce different compilation results from the same PL/SQL source. The exact compilation result depends on environment settings: compiler flags – either user defined or standard -, package constants or system parameters. This features also exposes a number of Compiler variables such as the line number being processed by the compiler and the name of the program unit being compiled. The values of these Compiler variables can be included in the compilation result. Finally, we can embed logic in our PL/SQL code that allows us to throw compiler errors when certain conditions are not met.

To summarize what Conditional PL/SQL Compilation entails:

1. Conditionally Include or Exclude pieces of PL/SQL code during compilation
2. Conditionally Raise Compilation Errors
3. Expand Compiler Keywords with Literal Values

The conditions referred to in items 1 and 2 are expressed using IF-THEN-ELSIF logic using expressions that make use of:

• System defined values or the Oracle initialization parameters for PL/SQL compilation: PLSQL_DEBUG, PLSQL_OPTIMIZE_LEVEL, PLSQL_CODE_TYPE, PLSQL_WARNINGS, and NLS_LENGTH_SEMANTICS
• User Defined Compiler Flags, set using ALTER SESSION SET PLSQL_CCFLAGS
• Package constants – PLS_INTEGER and BOOLEAN constants defined in package specifications

The compiler keywords hinted at in item 3 are PLSQL_LINE and PLSQL_UNIT, referred to as $$PLSQL_LINE and $$PLSQL_UNIT in our source code. PLSQL_UNIT is a VARCHAR2 literal value indicating the current source unit. For a named compilation unit, $$PLSQL_UNIT contains, but might not be limited to, the unit name. For an anonymous block, $$PLSQL_UNIT contains the empty string. PLSQL_LINE is a PLS_INTEGER literal value indicating the line number reference to $$PLSQL_LINE in the current unit.

Conditionally Including or Excluding Code

The PL/SQL Compiler in 10gR2 will recognize a number of Compiler Control Tokens as they are called. These are keywords, recognized by the Compiler and interpreted by a pre-processor that runs before the actual compilation is performed. That really means that the code that will be compiled is not necessarily the code that you presented to the PL/SQL Compiler and that is stored in the USER_SOURCE object. The pre-processor may take pieces of code out of the program unit, before the real compilation is done. So the code that is ‘active’ in your database may differ from what you find in USER_SOURCE or retrieve using dbms_metadata.get_ddl.

The Control Tokens are very straightforward: $IF, $THEN, $ELSIF, $END. These tokens are picked up by the preprocessor, and interpreted. The $IF and $ELSIF tokens are followed by expressions: Only static expressions which can be fully evaluated by the compiler; these static expressions must be evaluated to either TRUE or FALSE. Examples of valid expressions are:

• $$flag1 = ‘abcdef’
• package1.constant1
• $$flag2< 50 AND $$flag3 = ‘a’
• DBMS_DB_VERSION.VERSION < 10

Example code: In this first simple example, we have Debug Code in our PL/SQL unit. This code should be there for development and testing but not normally for the production environment: it adds a little overhead that for normal circumstances is not desired; however, even in production we may have situations where we want to be able to easily switch on that debug code, for example to analyze an incident. By wrapping the debug code inside Compiler Control Tokens that evaluatie some “compiler flag” we can have the PL/SQL Compiler’s preprocessor include or exclude the debug-code at the time of compilation. Note however that the debugcode remains part of the procedure and is stored in USER_SOURE.

create or replace procedure p
as
begin
  $IF $$debug_code
  $THEN
    dbms_output.put_line( 'Our debug code' );
    dbms_output.put_line( 'Would go here' );
  $END
  dbms_output.put_line( 'And our real code here' );
end;
/
exec p
And our real code here
PL/SQL procedure successfully completed.

Since we did not set the value of the ‘flag’ debug_code, it is defaulted to false and the debug-code is not included by the preprocessor. If we enable debug-code by explicitly setting the debug-code compiler flag to true, using either alter session set PLSQL_CCFLAGS=’flag:value,flag2:value’ or as follows by appending an instruction to the compile command – where it has only effect for this specific compile operation -, and recompile the debug-code will be enabled:

alter procedure P compile
plsql_ccflags = 'debug_code:true' reuse settings
/
exec p
Our debug code
Would go here
And our real code here

PL/SQL procedure successfully completed.

The flag names can be set to any unquoted PL/SQL identifier, including reserved words and keywords. If a flag value is explicitly set, it must be set to a TRUE, FALSE, PLS_INTEGER, or NULL. The flag names and values are not case sensitive.

The use of PLSQL_CCFLAGS is one way of specifying the value of Conditional Compilation Inquiry Directives. There is also a list of predefined Inquiry Directives: PLSQL_LINE, PLSQL_UNIT, PLSQL_CCFLAGS, PLSQL_CODE_TYPE, PLSQL_DEBUG, PLSQL_NATIVE_LIBRARY_DIR, PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT, PLSQL_OPTIMIZE_LEVEL, PLSQL_WARNINGS, and NLS_LENGTH_SEMANTICS. I am not sure whether other Oracle initialization parameters can be referenced as Inquriy Directives – I think not. These directives are referred to using $$

A third way of referencing values in the expressions interpreted by the pre-processor is the use of package constants. We can set up our own package specification with one or more BOOLEAN or PLS_INTEGER constants – VARCHAR2 is not allowed for this purpose – and reference these constants in our static expressions. I have created a small example: the package environment_settings contains a constant g_language. This constant indicates the locale for which all my PL/SQL code should be compiled; a value of 1 indicates the Dutch locale while 2 signifies English.

create or replace
package environment_settings
is
g_language constant PLS_INTEGER:=1; -- 1 is NL, 2 is EN
end;
/

Now I create a procedure p4 that is conditionally dependent on this constant g_language. That means that depending on the value of the constant at compile time, the compiled p4 will include either one or another section of code. Take careful note here that it is NOT a runtime dependency: the reference to g_language will not even be in the compiled code. The reference exists only in a Compiler Directive expression, that is chucked out at compilation time.

create or replace procedure p4
as
begin
 $IF environment_settings.g_language = 1
 $THEN
     dbms_output.put_line('Goedenmiddag. Of is het al avond?');
 $ELSIF environment_settings.g_language = 2
 $THEN
     dbms_output.put_line('Good afternoon. Or has the sun set and the evening fallen?');
 $END
end;
/
exec p4
Goedenmiddag. Of is het al avond?

Since g_language was 1, procedure p4 after compilation contains a single line of meaningful code: dbms_output.put_line('Goedenmiddag. Of is het al avond?');. If we change the constant g_language (english locale) and recompile the package:

create or replace
package environment_settings
is
g_language constant PLS_INTEGER := 2;
end;
/
exec p4
Good afternoon. Or has the sun set and the evening fallen

PL/SQL procedure successfully completed.

it is very interesting to see that without recompiling procedure p4, the result has changed. It almost seems like there is a runtime dependency! However – what actually happens is that the database records the dependency from procedure p4 on package environment_settings – since the dependencies are derived from the source and not the preprocessed source. When the package is recompiled, all dependent objects are invalidated so procedure p4 is invalid after recreating package environment_settings. Upon the first invocation of procedure p4 after that, p4 is recompiled. During this recompilation, the source is preprocessed afresh, using the values of the referenced package variables at that moment. Since g_language at this point is 2 (EN locale) the compiled code included the English output: dbms_output.put_line('Good afternoon. Or has the sun set and the evening fallen?');.

If you want to find out what source has been compiled after preprocessing is done, you can make use of the supplied package DBMS_PREPROCESSOR to get hold of the preprocessed source. Note: both USER_SOURCE and dbms_metadata.get_ddl will give you the PL/SQL source prior to preprocessing, so still inclusing all un-evaluatued compiler directives. The dbms_preprocessor package containts several overloaded versions of basically two program units:

• GET_POST_PROCESSED_SOURCE Functions that return the post-processed source text
• PRINT_POST_PROCESSED_SOURCE Procedures Prints post-processed source text

You can either pass in the name of an existing stored program unit or pass in a block of PL/SQL code to find out what the pre_processor would do to it. In the result of GET_POST_PROCESSED_SOURCE, all unselected lines of code are replaced with blank lines.

SET SERVEROUTPUT ON SIZE UNLIMITED

BEGIN
  DBMS_PREPROCESSOR.print_post_processed_source (
    object_type => 'PROCEDURE',
    schema_name => 'LUCAS',
    object_name => 'P4');
END;
/
procedure p4 as
begin
     dbms_output.put_line('Good afternoon. Or has the sun set and the evening fallen?');
end;
PL/SQL procedure successfully completed.

In the next example, I make the g_language constant derive its value at compile time from a compiler flag called language. This flag’s value is set prior to compilation:

alter session set plsql_ccflags='language:1'
/

Now I change the package code as follows:

create or replace
package environment_settings
is
g_language constant PLS_INTEGER := $$language;
end;
/

When I now execute procedure p4 we will have similar effect as saw earlier: package environment_settings was recompiled deriving the value of g_language from the compiler flag language, this invalidated p4 and when p4 is executed it is recompiled, using the new value of g_language in the compilation pre-process step.

exec p4
Goedenmiddag. Of is het al avond?

PL/SQL procedure successfully completed.

Now we change the language compiler flag value and recompile package environment_settings:

alter session set plsql_ccflags='language:2'
/
alter package environment_settings compile
/
exec p4
Good afternoon. Or has the sun set and the evening fallen?

PL/SQL procedure successfully completed.

In case you wonder what the compiler settings – included the values of the compiler flags – were at the time of compilation, you can consult the database dictionary view user_plsql_object_settings:

select *
from   user_plsql_object_settings
where  name ='P4'
/
NAME                           TYPE         PLSQL_OPTIMIZE_LEVEL
------------------------------ ------------ --------------------
PLSQL_CODE_TYPE
--------------------------------------------------------------------------
PLSQL_DEBUG
--------------------------------------------------------------------------
PLSQL_WARNINGS
--------------------------------------------------------------------------
NLS_LENGTH_SEMANTICS
--------------------------------------------------------------------------
PLSQL_CCFLAGS
--------------------------------------------------------------------------
P4                             PROCEDURE                       2
INTERPRETED
FALSE
DISABLE:ALL
BYTE

This view returns several values: the settings of the PL/SQL Compiler initialization parameters such as PLSQL_OPTIMIZE_LEVEL and PLSQL_DEBUG. In addition, the column PLSQL_CCFLAGS provides the values of all compiler flags as they applied at the time of compilation of the program unit. We see that no compiler flags applied to P4 when last compiled. That is correct of course, since P4 relied on the g_language constant – which is not a compiler flag. However, package environment_settings did make use of a Compiler Flag, as we see in this next query:

select *
from   user_plsql_object_settings
where  name ='ENVIRONMENT_SETTINGS'
/
NAME                           TYPE         PLSQL_OPTIMIZE_LEVEL
------------------------------ ------------ --------------------
PLSQL_CODE_TYPE
------------------------------------------------------------------
PLSQL_DEBUG
------------------------------------------------------------------
PLSQL_WARNINGS
------------------------------------------------------------------
NLS_LENGTH_SEMANTICS
------------------------------------------------------------------
PLSQL_CCFLAGS
------------------------------------------------------------------
ENVIRONMENT_SETTINGS           PACKAGE                         2
INTERPRETED
FALSE
DISABLE:ALL
BYTE
language:2

You can reuse the settings of compiler flags when you recompile a program unit with the following syntax:

alter package environment_settings compile reuse settings
/

Now I am wondering what happens with procedure that is implicitly compiled because of an invalidation of a package it depends upon: which values of compiler flags will be used then?

For example: procedure p4 not only depends on package environment_settings, but also on a compiler-flag:

create or replace procedure p4
as
begin
  $IF $$debug_mode
  $THEN
     dbms_output.put_line('Here is a line of debug info from '||$$PLSQL_UNIT||', to be specific from line '||$$PLSQL_LINE);
  $END
 $IF environment_settings.g_language = 1
 $THEN
     dbms_output.put_line('Goedenmiddag. Of is het al avond?');
 $ELSIF environment_settings.g_language = 2
 $THEN
     dbms_output.put_line('Good afternoon. Or has the sun set and the evening fallen?');
 $END
end;
/
exec p4
Good afternoon. Or has the sun set and the evening fallen?

PL/SQL procedure successfully completed.

Now I recompile p4 with debug_mode switched on:

alter procedure p4 compile PLSQL_CCFLAGS = 'debug_mode:TRUE'
/
exec p4
Here is a line of debug info from P4, to be specific from line 6
Good afternoon. Or has the sun set and the evening fallen?

PL/SQL procedure successfully completed.

At this point, I am going to recompile package environment_settings with a new value for the language compiler flag – changing back to locale NL. My question is: will P4 be recompiled with debug_mode is true – the flag value it has used for the currently active code – or with debug_mode is false, the current Session value.

alter session set plsql_ccflags='language:1'
/
alter package environment_settings compile
/
exec p4
Here is a line of debug info from P4, to be specific from line 6
Goedenmiddag. Of is het al avond?

PL/SQL procedure successfully completed.

The result is very clear: it has reused the settings that already applied – the ones we can find ourselves from USER_PLSQL_OBJECT_SETTINGS!

For more details on the exact definition and guidelines for static boolean expressions, see this section in the Using Static Expressions with Conditional Compilation.

Conditionally Raising Errors

Compiler errors are raised using the $ERROR some string that is displayed with show errors $END compiler directives. Typically the $ERROR directive is included in some $IF $THEN $END block – otherwise your code would never compile! An example is the following procedure that we only want to see compiled if the PLSQL_DEBUG has been switched off, as we do not want some smart DBA stepping through our code. (if that is your concern, better wrap the PL/SQL source).

create or replace procedure p2
as
begin
 $if $$plsql_debug
 $then
    $error 'This program must be compiled with PLSQL_DEBUG disabled' $end
 $end
 dbms_output.put_line( 'This is where it happens!' );
end;
/
Procedure created
ALTER SESSION SET PLSQL_DEBUG=TRUE
/
alter procedure p2 compile
/
Warning: Procedure altered with compilation errors.
show err
Errors for PROCEDURE P2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5      PLS-00179: $ERROR: This program must be compiled with PLSQL_DEBUG
         disabled

Expanding Compiler Keywords – Expanding Trace Calls during Compilation

A very simple example of the use of the keywords PLSQL_UNIT and PLSQL_LINE is this piece of code:

create or replace procedure p3
as
begin
 dbms_output.put_line( 'First a line of trace information, at line $$PLSQL_LINE in program unit '||$$PLSQL_UNIT||'.' );
 dbms_output.put_line( 'And now for some serious stuff' );
end;
/
exec p3
First a line of trace information, at line 4 in program unit P3.
And our real code here

Note that inside a package, PLSQL_UNIT resolves to the name of package, not the name of the procedure or function within the package.

Usages of Conditional Compilation

Now we have seen more or less what Conditional Compilation of PL/SQL looks like. The question now to be asked of course is: when will we use this and for what will we use this. Now I do not pretend to have come to the bottom of this question. I have this nagging feeling that Conditional Compilation opens up a lot of options.

At the same time, the one (and only) thing it really does is allowing us to move evaluation of conditions from runtime to compile time, thereby improving performance. It is not like we could not switch off pieces of code before, using if-then-else constructions referring to -runtime- values of package constants. With conditional compilation we move the burden of this evaluation to compile-time rather than run-time. Well, there is a little more than that: we can refer to PL/SQL Compiler settings in these evaluations, expand Line Number and Name of Program Unit and throw PL/SQL Compile errors if we do not like the environment settings.

So things you can consider doing with the functionality discussed in this article:

• You can leave debug code in your application now—and turn it on and off at will.
• You can program assertions as you might in C or Java: each procedure or function can test the values of its inputs, for example, and verify that they meet some criteria. These tests can be active during the whole development cycle and inactive for production. However, they remain as formal documentation of the conditions upon which the unit depends, and can be simply reactivated for debugging a production-time bug.
• You can prevent your code to compile if you do not like certain environment-settings, such as PLSQL_DEBUG, PLSQL_OPTIMIZE_LEVEL or a combination of user defined flags such as debug_mode, production_environment, unit_test_mode
• You can write database version-independent code: Program one package with code sections for version X and version Y; during compilation, depending on the ‘condition’, one of the two is compiled. Check out the new DBMS_DB_VERSION supplied package. However: despite this example being presented in all resources I have seen to date on this topic of Conditional Compilation, it seems to be that this will only be useful in database releases to come. We cannot use this feature for code sections for 9iR2 and 10gR2 respectively, as in the 9iR2 database our $IF directive is not recognized and our code will not compile at all – instead of compiling the code section targeted at 9iR2. Or am I missing something here?
• We can develop a single package with multiple code sections for specific locales, customers,O/S-platforms etc. One set of packages, conditional code sections per locale/customer/…
• You can support best practices during unit testing: For production, helper subprograms will be declared in a package body. For calls from a unit-testing framework, they are conditionally declared in the specification.

And I am sure there is much more than this.