Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

dbms_warning usage tips

Oracle Tips by Burleson


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."

(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.



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

Script Explanation:

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

 

Script Explanation:

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';

Query Result:

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!

Oracle training
 
 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational