10g PL/SQL Compiler Flags

18 Jan

10g PL/SQL Compiler Flags


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

Oracle’s 10g 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://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#i9084.

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 in 10g is an incredibly sophisticated bit of software which allows Oracle to optimize PL/SQL code, removing unreachable code, reuse of certain expression values, modifying the code to apply a wide range of techniques, etc.

There are three settings to this parameter, 0, 1 and 2, with 2 being the default and enabling the most optimizing features. Interestingly, in 10g, the PLS_INTEGER and BINARY_INTEGER types are semantically equivalent, setting this to 0 turns this equivalence off as well.

For further information, see http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams164.htm#REFRN10255.


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 Conditional Compilation.

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.


source :

http://www.oratechinfo.co.uk/plsql_compiler_flags.html

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: