Oracle DBMS_WARNING
Version 18.1.0.0

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Utility package supporting the functionality of PL/SQL warnings
AUTHID CURRENT_USER
Constants
Name Data Type Value
WARNING_CATEGORY VARCHAR2 ALL PERFORMANCE
INFORMATIONAL SEVERE
WARNING_VALUE VARCHAR2 DISABLE ERROR
ENABLE  
SCOPE VARCHAR2 SESSION SYSTEM
Current Warning Status set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE 'plsql%warn%';
Dependencies
ALL_PLSQL_OBJECT_SETTINGS DBA_PLSQL_OBJECT_SETTINGS DBMS_PLSQL_WARNING_LIB
ALL_WARNING_SETTINGS DBA_WARNING_SETTINGS USER_WARNING_SETTINGS
Documented Yes
First Available 10.1
Initialization Parameter PLSQL_WARNINGS
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsplsw.sql
Subprograms
 
ADD_WARNING_SETTING_CAT
Modify the current session's warning settings dbms_warning.add_warning_setting_cat(
warning_category IN VARCHAR2,
warning_value    IN VARCHAR2,
scope            IN VARCHAR2);
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

SELECT dbms_warning.get_warning_setting_string
FROM dual;

exec dbms_warning.add_warning_setting_cat('ALL', 'DISABLE', 'SESSION');

SELECT dbms_warning.get_warning_setting_string
FROM dual;
 
ADD_WARNING_SETTING_NUM
Modify the current session or system warning settings dbms_warning.add_warning_setting_num(
warning_number IN PLS_INTEGER,
warning_value  IN VARCHAR2,
scope          IN VARCHAR2);
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

SELECT dbms_warning.get_warning_setting_num(6002)
FROM dual;

exec dbms_warning.add_warning_setting_num(6002, 'DISABLE', 'SESSION');

SELECT dbms_warning.get_warning_setting_num(6002)
FROM dual;
 
GET_CATEGORY
Returns the category name given a message number dbms_warning.get_category(warning_number IN PLS_INTEGER)
RETURN VARCHAR2;
-- severe
SELECT dbms_warning.get_category(5000)
FROM dual;

-- informational
SELECT dbms_warning.get_category(6002)
FROM dual;

-- performance
SELECT dbms_warning.get_category(7203)
FROM dual;
 
GET_WARNING_SETTING_CAT
Returns the specific warning category setting for the current session dbms_warning.get_warning_setting_cat(warning_category IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_warning.get_warning_setting_cat('SEVERE')
FROM dual;

SELECT dbms_warning.get_warning_setting_cat('INFORMATIONAL')
FROM dual;

SELECT dbms_warning.get_warning_setting_cat('PERFORMANCE')
FROM dual;
 
GET_WARNING_SETTING_NUM
Returns the string required to enable the specific warning dbms_warning.get_warning_setting_num(warning_number IN PLS_INTEGER)
RETURN VARCHAR2;
SELECT dbms_warning.get_warning_setting_num(5000)
FROM dual;

SELECT dbms_warning.get_warning_setting_num(6002)
FROM dual;

SELECT dbms_warning.get_warning_setting_num(7203)
FROM dual;
 
GET_WARNING_SETTING_STRING
Returns the warning string for the current session dbms_warning.get_warning_setting_string RETURN VARCHAR2;
See SET_WARNING_SETTING_STRING demo
 
SET_WARNING_SETTING_STRING
Replaces previous settings with the new value and returns the current enabled settings dbms_warning.set_warning_setting.string(
value IN VARCHAR2,
scope IN VARCHAR2);
exec dbms_warning.set_warning_setting_string('ENABLE:ALL', 'SESSION');

SELECT dbms_warning.get_warning_setting_string
FROM dual;

Related Topics
Built-in Functions
Built-in Packages
DBMS_WARNING_INTERNAL
Errors
Exception Handling
Packages
PL/SQL Object Settings
PL/SQL Warnings
Tuning
What's New In 12cR2
What's New In 18cR1

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved