MOSC note 3558659 indicates that the dbms_warning
package does not support un-initialized collection types.
The DBMS_WARNING package provides a useful method for manipulating the behavior
of PL/SQL warning messages. With it, the DBA can define what warnings are
treated as errors, which are displayed, and which are suppressed.
add_warning_setting_cat is a procedure that permits modification of the previously
supplied warning category to a user specified value for the current session
or system?s warning settings. The valid categories are ALL, INFORMATIONAL,
SEVERE and PERFORMANCE. The valid values are ENABLE, DISABLE and ERROR. The
scope can be either SESSION or SYSTEM.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
WARNING_CATEGORY
|
VARCHAR2
|
IN
|
|
|
WARNING_VALUE
|
VARCHAR2
|
IN
|
|
|
SCOPE
|
VARCHAR2
|
IN
|
|
Table 7.206:
Add_warning_setting_cat Parameters
add_warning_setting_num is a procedure that permits modification of the previously
supplied warning number to a user specified value for the current session or
system?s warning settings. The valid values are ENABLE, DISABLE and ERROR.
The scope can be either SESSION or SYSTEM.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
WARNING_NUMBER
|
NUMBER
|
IN
|
|
|
WARNING_VALUE
|
VARCHAR2
|
IN
|
|
|
SCOPE
|
VARCHAR2
|
IN
|
|
Table 7.207:
Add_warning_setting_num Parameters
get_category
is a function that, for a given message number, returns the category as a
VARCHAR2.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
WARNING_NUMBER
|
PLS_INTEGER
|
IN
|
|
Table 7.208:
Get_category Parameter
get_warning_setting_cat is a function that, for the current session, returns the
warning value as a VARCHAR2.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
WARNING_CATEGORY
|
VARCHAR2
|
IN
|
|
Table 7.209:
Get_warning_setting_cat Parameters
get_warning_setting_num is a function
that, for the current session, returns the warning number as a VARCHAR2.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
WARNING_NUMBER
|
NUMBER
|
IN
|
|
Table 7.210:
Get_warning_setting_num Parameters
get_warning_setting_string is a function that, for the current session, returns the
warning string as a VARCHAR2. There are no parameters.
set_warning_setting_string is a
procedure that permits modification of the previous settings to a user
specified value for the current session or system?s warning settings. The
valid values are ENABLE, DISABLE and ERROR. The scope can be either SESSION
or SYSTEM.
|
ARGUMENT
|
TYPE
|
IN / OUT
|
DEFAULT VALUE
|
|
WARNING_VALUE
|
VARCHAR2
|
IN
|
|
|
SCOPE
|
VARCHAR2
|
IN
|
|
Table 7.211:
Set_warning_setting_string Parameters
============================================
From the version 10g, Oracle allows us to capture the
warnings generated by the compiler during a subprogram’s compilation. These
warnings are not that serious to raise a compilation error, but may result
in a run time error or a performance degradation. Examples of PL/SQL
warnings are,
1.
Unreachable code in a program unit.
2.
Using deprecated functionality.
3.
A reserved keyword used as a variable name.
The warnings can be enabled or disabled by setting
the database initialization parameter PLSQL_WARNINGS either by issuing an
ALTER statement or by using the dedicated DBMS_WARNING package.
The scope of the warnings can be determined by
executing the ALTER statement for either the SYSTEM or the SESSION level. In
the DBMS_WARNING package, the procedure ADD_WARNING_SETTING_CAT is used for
enabling or disabling a warning and has its third parameter dedicated for
defining its scope.
The prototype for the ALTER statement to enable/
disable the warning is shown below,
ALTER <SESSION | SYSTEM > SET
PLSQL_WARNINGS='[ENABLE | DISABLE | ERROR]: [ALL | SEVERE |
INFORMATIONAL | PERFORMANCE | WARNING_NUMBER]';
The prototype for the procedure
ADD_WARNING_SETTING_CAT to enable/ disable the warning is shown below,
DBMS_WARNING.ADD_WARNING_SETTING_CAT (warning_category
IN VARCHAR2, warning_value IN VARCHAR2, scope IN VARCHAR2);
Qualifier Values/
Warning Values
1.
Enable:
Enable a specific or a set of warnings.
2.
Disable: Disable a specific or a set of warnings.
3.
Error:
Treat a specific or a set of warnings as errors.
Modifier Values/
Warning Category
1.
All:
The warning messages in all categories are applied to the qualifier.
2.
Severe:
The warning messages in the severe category are applied to the qualifier.
3.
Informational: The warning messages in the informational category are
applied to the qualifier.
4.
Performance: The warning messages in the performance category are
applied to the qualifier.
Scope
1.
Session: The changes made to the database initialization parameter are
valid only for the particular session.
2.
System:
The changes made to the database initialization parameter are valid
throughout the system across all sessions.
To view the current warning settings, the below
PL/SQL script can be executed,
Exec DBMS_OUTPUT.PUT_LINE
(DBMS_WARNING.GET_WARNING_SETTING_STRING());
Result:
ENABLE:ALL
Enabling/ Disabling
a Warning
1.
To enable all warning messages.
·
System Level
ALTER SYSTEM SET plsql_warnings = 'enable:all';
Exec DBMS_WARNING.ADD_WARNING_SETTING_CAT
('Severe', 'Enable', 'System');
2.
To enable severe performance warnings.
·
Session Level
ALTER SESSION SET plsql_warnings = 'Enable:Severe',
'Enable:Performance';
1.
Exec DBMS_WARNING.ADD_WARNING_SETTING_CAT
('Performance', 'Enable', 'Session');
2.
Exec DBMS_WARNING.ADD_WARNING_SETTING_CAT
('Severe', 'Enable', 'Session');
3.
To enable all warnings and treat the
performance warnings as errors.
·
System Level
ALTER SYSTEM SET plsql_warnings = 'Enable:all', 'Error:Performance';
1.
Exec DBMS_WARNING.ADD_WARNING_SETTING_CAT
('All', 'Enable', 'System');
2.
Exec DBMS_WARNING.ADD_WARNING_SETTING_CAT
('Performance', 'Error', 'System');
4.
To enable the unreachable code warning using
its warning code.
·
Session Level
ALTER SESSION SET plsql_warnings =
'Enable:06002';
Exec DBMS_WARNING.ADD_WARNING_SETTING_CAT
('06002', 'Enable', 'Session');
Warning Code Range
The warning codes are prefixed with ‘PLW-’.
1.
The severe code is in the range of 05000 to
05999.
2.
The informational code is in the range of
06000 to 06999.
3.
The performance code is in the range of 07000
to 07249.
In the below example (Consider all the warnings have
been enabled for the current session), the reserved keyword NAME has been
used as a variable name which raises the below informational warning.
1.
CREATE OR REPLACE
2.
PROCEDURE proc_warning_test1
3.
IS
4.
name VARCHAR2(100):='Abdul Kalam';
5.
BEGIN
6.
DBMS_OUTPUT.PUT_LINE(name);
7.
END;
8.
/
PROCEDURE PROC_WARNING_TEST1 compiled
Warning: PLW-06010: keyword "NAME" used as a defined
name
|
Line No.
|
Description
|
|
1,2
|
The procedure
proc_warning_test1 is created.
|
|
3
|
Start of the declaration
section of the procedure.
|
|
4
|
A local variable name of
varchar2 data type with a precision of 100 bytes is declared and
assigned to the name ‘Abdul Kalam’.
|
|
5
|
Start of the execution
section of the block.
|
|
6
|
The local variable name’s
is printed using the dbms_output.put_line procedure.
|
|
7,8
|
End of the execution
section of the block.
|
In the below example (Consider all the warnings have
been enabled for the current session), an informational warning is raised as
a part of the code is un-reachable.
1.
CREATE OR REPLACE
2.
PROCEDURE proc_warning_test2
3.
IS
4.
L_B_VAR1 CONSTANT BOOLEAN:= TRUE;
5.
BEGIN
6.
IF l_b_var1 THEN
7.
DBMS_OUTPUT.PUT_LINE('The Boolean value
is True');
8.
ELSE
9.
DBMS_OUTPUT.PUT_LINE('The Boolean value
is False');
10.
END IF;
11.
END;
12.
/
PROCEDURE PROC_WARNING_TEST2 compiled
Warning: PLW-06002: Unreachable Code
|
Line No.
|
Description
|
|
1,2
|
The procedure
proc_warning_test2 is created.
|
|
3
|
Start of the declaration
section of the procedure.
|
|
4
|
A local variable l_b_var1
of the Boolean data type is declared and assigned to the value TRUE.
|
|
5
|
Start of the execution
section of the block.
|
|
6
|
Start of the IF statement
checking whether the local variable l_b_var1 is true or not.
|
|
7
|
If the condition of the IF
statement in the line 6 is satisfied, the default text stating that
the Boolean value as True is printed using the dbms_output.put_line
procedure.
|
|
8
|
If the IF condition in the
line 6 is not satisfied, the default text stating that the Boolean
value as False is printed using the dbms_output.put_line procedure.
|
|
9
|
End of the IF statement.
|
|
10
|
End of the execution
statement of the block.
|
After the warnings have been enabled using the ALTER
statement or using the inbuilt package, the procedure for which the warning
has to be verified has to be compiled if it already exists in the database
as like below,
Alter procedure Proc_warning_test1 compile;
Once, after the procedure is compiled, the warnings
raised for this procedure can be verified in the ERRORS data dictionary view
with the attribute value set to WARNING as like below,
SELECT NAME,
TYPE,
LINE,
TEXT,
ATTRIBUTE,
MESSAGE_NUMBER
FROM user_errors
WHERE name='PROC_WARNING_TEST1'
And attribute='WARNING';
|
NAME
|
TYPE
|
LINE
|
TEXT
|
ATTRIBUTE
|
MESSAGE_NUMBER
|
|
PROC_WARNING_TEST1
|
PROCEDURE
|
3
|
PLW-06010: keyword "NAME"
used as a defined name
|
WARNING
|
6010
|
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
