 |
|
dbms_warning usage tips
Oracle Tips by Burleson Consulting
November 5, 2007
|
The dbms_warning built-in package is used in 11g to
manipulate the default PL/SQL warning messages, in conjunction with the new 11g
plsql_warnings parameter.
According to Steve Feuerstein, "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."
Dr. Tim Hall shows this example of the dbms_warning
package:
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
CREATE OR REPLACE PROCEDURE test_warnings AS
l_dummy VARCHAR2(10) := '1';
BEGIN
IF 1=1 THEN
SELECT '2'
INTO l_dummy
FROM dual;
ELSE
RAISE_APPLICATION_ERROR(-20000, 'l_dummy != 1!');
END IF;
END;
/
SP2-0804: Procedure created with compilation warnings
SHOW ERRORS
LINE/COL ERROR
-------- ---------------------------
9/5 PLW-06002: Unreachable code
The docs note that dbms_warning allow you to enable and
disable PL/SQL warning messages at-will:
"Oracle can issue warnings when you compile subprograms
that produce ambiguous results or use inefficient constructs.
You can selectively enable and disable
these warnings through the PLSQL_WARNINGS initialization parameter and the
DBMS_WARNING package."
O'Reilly Publishing (Steve Feuerstein) has these good notes on enabling and
disabling error messages with dbms_warning:
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;
MOSC note 3558659 indicates that the dbms_warning
package does not support un-initialized collection types.
