General Information
Library Note
Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx .
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
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;