DBSecWorx: DBMS

Oracle DBMS_WARNING Built-In Package Versions 10.1 - 19.3

Security Advisory

This package provides an API for managing and monitoring the status of PL/SQL warning messages.

Recommended Security Rules

NEVER

Deploy or manage a database with PL/SQL warnings disabled. Oracle's default configuration with warnings disabled is exactly what you do not want to do.

WITH GREAT CARE

EXECUTE on this package is granted to PUBLIC as if there was a rational reason why any unprivileged end-user should have the right to enable or disable warning message. We recommend revoking the grant to PUBLIC and replacing it with a grant to the DBA role or, even better, replacing it with an explicit grant to the members of your DBA team.
Remember to always validate dropping a grant to PUBLIC in a preproduction environment before rolling that change into production.

CAUTIONS

This package is documented in the Oracle Types and Packages reference manual and supported by Oracle Support. Use of PL/SQL warnings is also part of your existing Oracle license agreement no matter which version, 10.1 or above, or which edition you are licensed to install. If you are not using PL/SQL Warnings in every one of your production databases ... our caution is that you should be.

How Oracle Works

The code examples below are sufficient to demonstrate real-world use of this package. Detailed information about PL/SQL warnings can be found on our PL/SQL Warnings page linked at the bottom of this page.

Determine your 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%'; NAME VALUE ------------------------------ ------------------------------ plsql_warnings ENABLE:ALL -- The default on installation is DISABLE:ALL and there is no rational reason to not alter that during the installation process using DBCA.

DBMS_WARNING Package Information

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

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