Compiling PL/SQL Code for an Oracle Database - How to turn on compile-time warnings (Page 4 of 4 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter 20 of the book Oracle PL/SQL Programming, Fourth Edition, written by Steven Feuerstein and Bill Pribyl (O'Reilly; ISBN: 0596009771). Check it out today at your favorite bookstore. Buy this book now.
|
|
