Oracle DBMS_WARNING Demos

Oracle DBMS_WARNING Version 12.1.0

General Information

Library Note

Morgan's Library Page Footer

This demo can be run in any 12c Database whether CDB or Traditional now let's see what happens when I keep typing to the point that this all runs into multiple lines

Purpose

Utility package supporting the functionality of PL/SQL warnings

AUTHID

CURRENT_USER

Constants

Name	Data Type	Value
WARNING_CATEGORY	VARCHAR2	ALL	PERFORMANCE
WARNING_CATEGORY	VARCHAR2	INFORMATIONAL	SEVERE
WARNING_VALUE	VARCHAR2	DISABLE	ERROR
WARNING_VALUE	VARCHAR2	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	GET_WARNING_SETTING_CAT	GET_WARNING_SETTING_STRING
ADD_WARNING_SETTING_NUM	GET_WARNING_SETTING_NUM	SET_WARNING_SETTING_STRING
GET_CATEGORY

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 specific warning number setting for the current session

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

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;