1.1 Introduction

To make your programs more robust and avoid problems at run time, you can turn on checking for certain warning conditions. These conditions are not serious enough to produce an error and keep you from compiling a subprogram. They might point out something in the subprogram that produces an undefined result or might create a performance problem.
To work with PL/SQL warning messages, you use the PLSQL_WARNINGS initialization parameter, the DBMS_WARNING package, and the USER/DBA/ALL_PLSQL_OBJECT_SETTINGS
Controlling PL/SQL Warning Messages
PL/SQL warning messages are divided into categories, so that you can suppress or display groups of similar warnings during compilation. The categories are:
· SEVERE: Messages for conditions that might cause unexpected behavior or wrong results, such as aliasing problems with parameters.
· PERFORMANCE: Messages for conditions that might cause performance problems, such as passing a VARCHAR2 value to a NUMBER column in an INSERT statement.
· INFORMATIONAL: Messages for conditions that do not have an effect on performance or correctness, but that you might want to change to make the code more maintainable, such as unreachable code that can never be executed.
· ALL: is a shorthand way to refer to all warning messages.
You can also treat particular messages as errors instead of warnings. For example, if you know that the warning message PLW-05003 represents a serious problem in your code, including 'ERROR:05003' in the PLSQL_WARNINGS setting makes that condition trigger an error message (PLS_05003) instead of a warning message. An error message causes the compilation to fail.

1.2 PLSQL_WARNINGS parameter

PLSQL_WARNINGS enables or disables the reporting of warning messages by the PL/SQL compiler, and specifies which warning messages to show as errors.
Syntax PLSQL_WARNINGS = 'value_clause' [, 'value_clause' ] ...
value_clause::=
{ ENABLE | DISABLE | ERROR }:
{ ALL
| SEVERE
| INFORMATIONAL
| PERFORMANCE
| { integer
| (integer [, integer ] ...)
}
}
Multiple value clauses may be specified, enclosed in quotes and separated by commas.
Each value clause is composed of a qualifier, a colon (:), and a modifier.
Default value 'DISABLE:ALL'
Modifiable ALTER SESSION, ALTER SYSTEM
Examples PLSQL_WARNINGS = 'ENABLE:SEVERE', 'DISABLE:INFORMATIONAL';
PLSQL_WARNINGS = 'DISABLE:ALL';
PLSQL_WARNINGS = 'DISABLE:5000', 'ENABLE:5001',
'ERROR:5002';
PLSQL_WARNINGS = 'ENABLE:(5000,5001,5002)',
'DISABLE:(6000,6001)';
PLSQL_WARNINGS enables or disables the reporting of warning messages by the PL/SQL compiler, and specifies which warning messages to show as errors.
Qualifier values:
■ ENABLE
Enable a specific warning or a set of warnings
■ DISABLE
Disable a specific warning or a set of warnings
■ ERROR
Treat a specific warning or a set of warnings as errors
Modifier values:
■ ALL
Apply the qualifier to all warning messages
■ SEVERE
Apply the qualifier to only those warning messages in the SEVERE category
■ INFORMATIONAL
Apply the qualifier to only those warning messages in the INFORMATIONAL
category
■ PERFORMANCE
Apply the qualifier to only those warning messages in the PERFORMANCE category To let the database issue warning messages during PL/SQL compilation, you set the initialization parameter PLSQL_WARNINGS.
You can enable and disable entire categories of warnings (ALL, SEVERE, INFORMATIONAL, PERFORMANCE), enable and disable specific message numbers, and make the database treat certain warnings as compilation errors so that those conditions must be corrected. This parameter can be set at the system level or the session level. You can also set it for a single compilation by including it as part of the ALTER PROCEDURE ... COMPILE statement. You might turn on all warnings during development, turn off all warnings when deploying for production, or turn on some warnings when working on a particular subprogram where you are concerned with some aspect, such as unnecessary code or performance.

1.3 Recompilation

ALTER … RECOMPILE REUSE SETTINGS.
Warning messages can be issued during compilation of PL/SQL subprograms; anonymous blocks do not produce any warnings.
The settings for the PLSQL_WARNINGS parameter are stored along with each compiled subprogram. If you recompile the subprogram with a CREATE OR REPLACE statement, the current settings for that session are used. If you recompile the subprogram with an ALTER ... COMPILE statement, the current session setting might be used, or the original setting that was stored with the subprogram, depending on whether you include the REUSE SETTINGS clause in the statement.
To see any warnings generated during compilation, you use the SQL*Plus SHOW ERRORS command or query the USER_ERRORS data dictionary view. PL/SQL warning messages all use the prefix PLW.
-- To focus on one aspect
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';
-- Recompile with extra checking
ALTER PROCEDURE loc_var COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE'
REUSE SETTINGS;
-- To turn off all warnings
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
-- Display 'severe' warnings, don't want 'performance' warnings, and
-- want PLW-06002 warnings to produce errors that halt compilation
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE',
'ERROR:06002';
-- For debugging during development
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

1.4 DBMS_WARNING Package

If you are writing a development environment that compiles PL/SQL subprograms, you can control PL/SQL warning messages by calling subprograms in the DBMS_WARNING package. You might also use this package when compiling a complex application, made up of several nested SQL*Plus scripts, where different warning settings apply to different subprograms. You can save the current state of the PLSQL_WARNINGS parameter with one call to the package, change the parameter to compile a particular set of subprograms, then restore the original parameter value.
-- When warnings disabled, the following procedure compiles with no warnings
CREATE OR REPLACE PROCEDURE unreachable_code AS
x CONSTANT BOOLEAN := TRUE;
BEGIN
IF x THEN
DBMS_OUTPUT.PUT_LINE('TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('FALSE');
END IF;
END unreachable_code;
/
-- enable all warning messages for this session
CALL DBMS_WARNING.set_warning_setting_string('ENABLE:ALL' ,'SESSION');
-- Check the current warning setting
SELECT DBMS_WARNING.get_warning_setting_string() FROM DUAL;
-- Recompile the procedure and a warning about unreachable code displays ALTER PROCEDURE unreachable_code COMPILE;
SHOW ERRORS;
ALTER PROCEDURE unreachable_code COMPILE
PLSQL_WARNINGS = 'ENABLE:ALL' REUSE SETTINGS;

The DBMS_WARNING package provides a way to manipulate the behavior of PL/SQL warning messages, in particular by reading and changing the setting of the PLSQL_WARNINGS initialization parameter to control what kinds of warnings are suppressed, displayed, or treated as errors. This package provides the interface to query, modify and delete current system or session settings.

1.4.1 ADD_WARNING_SETTING_CAT Procedure

You can modify the current session's or system's warning settings with the value supplied. The value will be added to the existing parameter setting if the value for the warning_category or warning_value has not been set, or override the existing value. The effect of calling this function is same as adding the qualifier (ENABLE/DISABLE/ERROR) on the category specified to the end of the current session or system setting.
Syntax
DBMS_WARNING.ADD_WARNING_SETTING_CAT (
warning_category IN VARCHAR2,
warning_value IN VARCHAR2,
scope IN VARCAHR2);
warning_category Name of the category. Allowed values are ALL, INFORMATIONAL, SEVERE and PERFORMANCE.
warning_value Value for the category. Allowed values are ENABLE, DISABLE, and ERROR.
scope Specifies if the changes are being performed in the session context or the system context. Allowed values are SESSION or SYSTEM.

1.4.2 ADD_WARNING_SETTING_NUM Procedure

You can modify the current session or system warning settings with the value supplied. If the value was already set, you will override the existing value. The effect of calling this function is same as adding the qualifier (ENABLE / DISABLE/ ERROR) on the category specified to the end of the current session or system setting.
Syntax
DBMS_WARNING.ADD_WARNING_SETTING_NUM (
warning_number IN NUMBER,
warning_value IN VARCHAR2,
scope IN VARCAHR2);
warning_number The warning number. Allowed values are all valid warning numbers.
warning_value Value for the category. Allowed values are ENABLE, DISABLE, and ERROR.
scope Specifies if the changes are being performed in the session context or the system context. Allowed values are SESSION or SYSTEM.

1.4.3 GET_CATEGORY Function

This function returns the category name, given the message number.
DBMS_WARNING.GET_CATEGORY (
warning_number IN pls_integer)
RETURN VARCHAR2;
warning_number The warning message number.

1.4.4 GET_WARNING_SETTING_CAT Function

This function returns the specific warning category setting for the current session.
DBMS_WARNING.GET_WARNING_SETTING_CAT (
warning_category IN VARCHAR2)
RETURN warning_value;
warning_category Name of the category. Allowed values are all valid category names (ALL, INFORMATIONAL, SEVERE and PERFORMANCE).

1.4.5 GET_WARNING_SETTING_NUM Function

This function returns the specific warning number setting for the current session.
DBMS_WARNING.GET_WARNING_SETTING_NUM (
warning_number IN NUMBER)
RETURN warning_value;
warning_number Warning number. Allowed values are all valid warning
numbers.

1.4.6 GET_WARNING_SETTING_STRING Function

This function returns the entire warning string for the current session.
DBMS_WARNING.GET_WARNING_SETTING_STRING
RETURN pls_integer;
Use this function when you do not have SELECT privilege on v$parameter or v$paramater2 fixed tables, or if you want to parse the warning string yourself and then modify and set the new value using SET_WARNING_SETTING_STRING.

1.4.7 SET_WARNING_SETTING_STRING Procedure

This procedureS replaces previous settings with the new value. The warning string may contain mix of category and warning numbers using the same syntax as used on the right hand side of '=' when issuing an ALTER SESSION or SYSTEM SET PLSQL_WARNINGS command. This will have same effect as ALTER SESSION OR ALTER SYSTEM command.
DBMS_WARNING.SET_WARNING_SETTING_STRING (
warning_value IN VARCHAR2,
scope IN VARCHAR2);
warning_value The new string that will constitute the new value.
scope This will specify if the changes are being done in the session
context, or system context. Allowed values are SESSION or
SYSTEM.

1.5 Examples

SQL> ALTER SYSTEM SET PLSQL_WARNINGS = 'ENABLE:ALL' SCOPE=BOTH;
SQL> ALTER FUNCTION OYM.DB_CARGA_BLOQUE_FUENTES COMPILE;
SQL> SELECT * FROM DBA_ERRORS WHERE NAME='DB_CARGA_BLOQUE_FUENTES';
OWNER NAME TYPE SEQUENCE LINE
POSITION TEXT ATTRIBUTE MESSAGE_NUMBER
OYM DB_CARGA_BLOQUE_FUENTES FUNCTION
1 11 26
PLW-07204: puede que la conversi¾n que no sea de tipo de columna dÚ como resultado un plan de cónsulta sub¾ptimo
WARNING 7204
OYM DB_CARGA_BLOQUE_FUENTES FUNCTION
2 11 28
PLW-07204: puede que la conversi¾n que no sea de tipo de columna dÚ como resulta
do un plan de cónsul ta sub¾ptimo
WARNING 7204
SQL> SELECT DBMS_WARNING.GET_CATEGORY(07204) FROM DUAL;
DBMS_WARNING.GET_CATEGORY(07204)
--------------------
PERFORMANCE