How to turn on compile-time warnings - Oracle

Oracle allows you to turn compile-time warnings on and off, and also to specify the type of warnings that interest you. There are three categories of warnings:

Severe
   Conditions that could cause unexpected behavior or
   actual wrong results, such as aliasing problems with
   parameters

Performance
   Conditions that could cause performance problems,
   such as passing a VARCHAR2 value to a NUMBER
   column in an UPDATE statement

Informational
   Conditions that do not affect performance or
   correctness, but that you might want to change to
   make the code more maintainable

Oracle lets you enable/disable compile-time warnings for a specific category, for all categories, and even for specific, individual warnings. You can do this with either the ALTER DDL command or the DBMS_WARNING built-in package.

To turn on compile-time warnings in your system as a whole, issue this command:

  ALTER SYSTEM SET PLSQL_WARNINGS='string'

The following command, for example, turns on compile-time warnings in your system for all categories:

  ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL';

This is a useful setting to have in place during development because it will catch the largest number of potential issues in your code.

To turn on compile-time warnings in your session for severe problems only, issue this command:

  ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE';

And if you want to alter compile-time warnings settings for a particular, already-compiled program, you can issue a command like this:

  ALTER PROCEDURE hello COMPILE PLSQL_WARNINGS='ENABLE:ALL' REUSE SETTINGS;

Make sure to include REUSE SETTINGS to make sure that all other settings (such as the optimization level) are not affected by the ALTER command.

You can tweak your settings with a very high level of granularity by combining different options. For example, suppose that I want to see all performance-related issues, that I will not concern myself with server issues for the moment, and that I would like the compiler to treat PLW-05005: function exited without a RETURN as a compile error. I would then issue this command:

  ALTER SESSION SET PLSQL_WARNINGS=
    'DISABLE:SEVERE'
   ,'ENABLE:PERFORMANCE'
   ,'ERROR:05005';

I especially like this “treat as error” option. Consider the PLW-05005: function returns without value warning. If I leave PLW-05005 simply as a warning, then when I compile my no_return function, shown below, the program does compile, and I can use it in my application.

  SQL> CREATE OR REPLACE FUNCTION no_return
    2     RETURN VARCHAR2
    3  AS
    4  BEGIN
    5     DBMS_OUTPUT.PUT_LINE (
    6        'Here I am, here I stay');
    7  END no_return;
    8  /

  SP2-0806: Function created with compilation warnings

  SQL> sho err
  Errors for FUNCTION NO_RETURN:  

  LINE/COL ERROR 
  -------- ---------------------------------
  1/1      PLW-05005: function NO_RETURN
           returns without value at line 7

If I now alter the treatment of that error with the ALTER SESSION command shown above and then recompile no_return, the compiler stops me in my tracks:

  Warning: Procedure altered with compilation errors

By the way, I could also change the settings for that particular program only, to flag this warning as a “hard” error with a command like this:

  ALTER PROCEDURE no_return COMPILE PLSQL_WARNINGS = 'error:6002' REUSE SETTINGS
  /

This ability to treat a warning as an error did not work in 10.1.0.2; this program was fixed in Oracle Database 10g Release 2 and is reported to be back-ported to 10.1.0.3.

You can, in each of these variations of the ALTER command, also specify ALL as a quick and easy way to refer to all compile-time warnings categories, as in:

  ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Oracle also provides the DBMS_WARNING package, which provides the same capabilities to set and change compile-time warning settings through a PL/SQL API. DBMS_WARNING also goes beyond the ALTER command, allowing you to make changes to those warning controls that you care about while leaving all the others intact. You can also easily restore the original settings when you’re done.

DBMS_WARNING was designed to be used in install scripts in which you might need to disable a certain warning, or treat a warning as an error, for individual program units being compiled. You might not have any control over the scripts surrounding those for which you are responsible. Each script’s author should be able to set the warning settings he wants, while inheriting a broader set of settings from a more global scope.

Please check back next week for the continuation of this article.