PL/SQL Compiler Flags

PLSQL_WARNINGS
PLSQL_OPTIMIZE_LEVEL
PLSQL_CCFLAGS

As of 10g, Oracle have introduced a series of compiler options. All of the following flags and their values can be seen by querying the [DBA | ALL | USER]_PLSQL_OBJECT_SETTINGS view.

PLSQL_WARNINGS

The PL/SQL compiler can now give a series of warnings about any PL/SQL code being compiled. Note, these warnings are only generated for PL/SQL subprograms, they are not generated for anonymous PL/SQL blocks.

There are three different "classes" of warnings :

INFORMATIONAL - Such as unreachable code, etc.
PERFORMANCE - Checking mismatches in datatypes etc.
SEVERE - Problems which may cause unexpected behaviour, such as incorrect aliasing etc.

You can specify ALL to turn them all on or off.

There are a few ways you can specify these flags, either as an ALTER SESSION command, using the DBMS_WARNING package or as part of the ALTER .. COMPILE command. Usually, most people use ALL, but here's an example of turning PERFORMANCE on and SEVERE off :

SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE';

Session altered.

For more information, see http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams186.htm#REFRN10249.

It's best shown with an example or two.

Here's an example of how Oracle can work out if you've got any unreachable code (i.e. code which can never be executed) :

SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Session altered.

SQL> CREATE OR REPLACE PROCEDURE p
  2  IS
  3  BEGIN
  4    IF 1=2 THEN NULL;
  5    END IF;
  6  END p;
  7  /

SP2-0804: Procedure created with compilation warnings

SQL> SHO ERR
Errors for PROCEDURE P:

4/15     PLW-06002: Unreachable code

And here's an example of a PERFORMANCE type error (here, inserting a number into a VARCHAR2 field) :

SQL> CREATE TABLE t ( a VARCHAR2(10) );

Table created.

SQL> CREATE OR REPLACE PROCEDURE p
  2  IS
  3  BEGIN
  4    INSERT INTO t VALUES ( 10 );
  5  END p;
  6  /

SP2-0804: Procedure created with compilation warnings

SQL> SHO ERR
Errors for PROCEDURE P:

4/26     PLW-07202: bind type would result in conversion away from column
         type

PLSQL_OPTIMIZE_LEVEL

The PL/SQL optimizing compiler from 10g onwards is an incredibly sophisticated bit of software which allows Oracle to optimize PL/SQL code, remove unreachable code, reuse certain expression values, modify the code to apply a wide range of techniques, etc.

As of 11g, there are four settings to this parameter, 0, 1, 2 and 3, with 2 being the default.

Intraunit Inlining

Of particular interest in optimization level 3 is the enabling of intraunit inlining, which is an optimization technique for removing calls to other stored routines and replacing them with a copy of the code. This allows for performance enhancement because of the reduction in "call overhead", i.e. the overhead of calling one PL/SQL routine from another.

To enable it for a given stored PL/SQL program unit, you simply :

SQL> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3;

and then recompile the program unit. IF you enable PL/SQL warnings and do the recompilation, then, if the PL/SQL optimizer has inlined parts of the code, you will get a message of the form:

96/43    PLW-06005: inlining of call of procedure 'TEST' was done

You can determine the optimization level of a stored program unit by looking at the [USER|ALL|DBA]_PLSQL_OBJECT_SETTINGS view.

For further information, see http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams184.htm.

PLSQL_CCFLAGS

PLSQL_CCFLAGS allows conditional compilation, which basically means that the structure of a PL/SQL subprogram can be effectively changed by specifying a value at compile time.

PLSQL_CCFLAGS allows compile-time specification of values which can be used within the PL/SQL subprogram. There is a special $ syntax for specifying both the conditional operations, $if, $elsif, $end etc. and the values themselves, $$debug_mode etc., the syntax is :

$IF boolean_static_expression $THEN text
  [ $ELSIF boolean_static_expression $THEN text ]
  [ $ELSE text ]
$END

For further information, see http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#LNPLS00210.

Here is an example of PLSQL_CCFLAGS being used.

SQL> CREATE OR REPLACE PROCEDURE test_plsql_ccflags
  2  IS
  3  BEGIN
  4    $IF $$debug_mode $THEN DBMS_OUTPUT.PUT_LINE('DEBUGGING'); $END
  5    NULL;
  6  END test_plsql_ccflags;
  7  /

Procedure created.

SQL> ALTER PROCEDURE test_plsql_ccflags COMPILE PLSQL_CCFLAGS='debug_mode:false' REUSE SETTINGS;

Procedure altered.

SQL> EXEC test_plsql_ccflags;

PL/SQL procedure successfully completed.

SQL> ALTER PROCEDURE test_plsql_ccflags COMPILE PLSQL_CCFLAGS='debug_mode:true' REUSE SETTINGS;

Procedure altered.

SQL> EXEC test_plsql_ccflags;
DEBUGGING

PL/SQL procedure successfully completed.

Important thing to note, these values are "compile-time" settings, they are not globals, if the value of one of them changes, the PL/SQL subprogram must be recompiled to see the new value, i.e. I'll use ALTER SESSION without recompilation here to show this :

SQL> ALTER SESSION SET PLSQL_CCFLAGS='debug_mode:true';

Session altered.

SQL> CREATE OR REPLACE PROCEDURE test_plsql_ccflags
  2  IS
  3  BEGIN
  4    $if $$debug_mode $then dbms_output.put_line('DEBUGGING'); $end
  5    NULL;
  6  END test_plsql_ccflags;
  7  /

Procedure created.

SQL> EXEC test_plsql_ccflags;
DEBUGGING

PL/SQL procedure successfully completed.

SQL> ALTER SESSION SET PLSQL_CCFLAGS='debug_mode:false';

Session altered.

SQL> EXEC test_plsql_ccflags;
DEBUGGING

PL/SQL procedure successfully completed.

The change of "debug_mode" to false does not affect the fact that to test_plsql_ccflags, it's still true, and that's because it was compiled with it set to true, i.e. however, a recompile picks up the new value :

SQL> ALTER SESSION SET PLSQL_CCFLAGS='debug_mode:false';

Session altered.

SQL> ALTER PROCEDURE test_plsql_ccflags COMPILE;

Procedure altered.

SQL> EXEC test_plsql_ccflags;

PL/SQL procedure successfully completed.

Now, set it to true and recompile :

SQL> ALTER SESSION SET PLSQL_CCFLAGS='debug_mode:true';

Session altered.

SQL> ALTER PROCEDURE test_plsql_ccflags COMPILE;

Procedure altered.

SQL> EXEC test_plsql_ccflags;
DEBUGGING

PL/SQL procedure successfully completed.

Database versioning

With the advent of PL/SQL conditional compilation, there is a useful technique for utilising new features of 10g, while still remaining faithful to 9i with the same source code. There is a package, DBMS_DB_VERSION, which allows you to get the current version number of the database, i.e.

SQL> BEGIN
  2    dbms_output.put_line(DBMS_DB_VERSION.VERSION);
  3  END;
  4  /
10

PL/SQL procedure successfully completed.

This can be used to enable 10g only features when using 10g, and not if using any other version! Here's an example of enabling use of the BINARY_FLOAT datatype (a 10g only datatype) but only when using 10g, the variable is declared as NUMBER otherwise!

SQL> CREATE OR REPLACE PROCEDURE test_plsql_ccflags
  2  IS
  3    l_x    $if dbms_db_version.version >= 10 $then BINARY_FLOAT;
  4           $else                                   NUMBER;
  5           $end
  6  BEGIN
  7    NULL;
  8  END test_plsql_ccflags;
  9  /

Procedure created.

As you can see, there's astonishing scope for version-independent coding.

Conditional Errors

You can use the $ERROR compiler flag to raise an compile-time error if certain conditions are met, here, an error is raised if debug_mode is set to true.

SQL> ALTER SESSION SET PLSQL_CCFLAGS='debug_mode:false';

Session altered.

SQL> CREATE OR REPLACE PROCEDURE test_plsql_ccflags
  2  IS
  3  BEGIN
  4  $if $$debug_mode $then $ERROR 'debug_mode is on' $end $end
  5    NULL;
  6  END test_plsql_ccflags;
  7  /

Procedure created.

It compiles OK when debug_mode is off, now let's recompile with it on :

SQL> ALTER PROCEDURE test_plsql_ccflags COMPILE PLSQL_CCFLAGS='debug_mode:true' REUSE SETTINGS;

Warning: Procedure altered with compilation errors.

SQL> sho err
Errors for PROCEDURE TEST_PLSQL_CCFLAGS:

4/24     PLS-00179: $ERROR: debug_mode is on

Using DBMS_PREPROCESSOR

You can use the DBMS_PREPROCESSOR package to examine the resultant source code after compilation, here using a slightly modified version of TEST_PLSQL_CCFLAGS above which conditionally declares a variable of either BINARY_FLOAT or NUMBER, i.e.

SQL> CREATE OR REPLACE PROCEDURE test_plsql_ccflags
  2  IS
  3    l_x    $if $$debug_mode $then BINARY_FLOAT;
  4           $else NUMBER;
  5           $end
  6  BEGIN
  7    NULL;
  8  END test_plsql_ccflags;
  9  /

Procedure created.

SQL> EXEC dbms_preprocessor.print_post_processed_source('PROCEDURE', 'MARTIN', 'TEST_PLSQL_CCFLAGS')

PROCEDURE test_plsql_ccflags
IS
l_x
NUMBER;
BEGIN
NULL;
END test_plsql_ccflags;

PL/SQL procedure successfully completed.

SQL> ALTER PROCEDURE test_plsql_ccflags COMPILE PLSQL_CCFLAGS='debug_mode:true' REUSE SETTINGS;

Procedure altered.

SQL> EXEC dbms_preprocessor.print_post_processed_source('PROCEDURE', 'MARTIN', 'TEST_PLSQL_CCFLAGS')

PROCEDURE test_plsql_ccflags
IS
l_x                           BINARY_FLOAT;
BEGIN
NULL;
END test_plsql_ccflags;

PL/SQL procedure successfully completed.

Note, also, no compiler flag information. This is key and the strength of the whole conditional compilation technology. The PL/SQL simply isn't there after compilation.