Oracle | DBMS

Oracle DBMS_WARNING Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Utility package supporting the functionality of PL/SQL warnings

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
WARNING_CATEGORY	VARCHAR2	ALL
		INFORMATIONAL
		PERFORMANCE
		SEVERE
WARNING_VALUE	VARCHAR2	DISABLE
		ENABLE
		ERROR
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

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA (default, READ_ONLY);

Security Model Owned by SYS with EXECUTE granted to PUBLIC with GRANT OPION

Consider the above security model. Oracle has granted execute to PUBLIC. How, precisely, could you grant EXECUTE to anyone that doesn't already have it? And, why would PUBLIC ever execute this package.

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