CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_WARNING

Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsplsw.sql
First Availability 10.1
Constants
Name Data Type Values
WARNING_CATEGORY VARCHAR2
ALL PERFORMANCE
INFORMATIONAL SEVERE
WARNING_VALUE VARCHAR2
DISABLE ERROR
ENABLE
SCOPE VARCHAR2
SESSION SYSTEM
Dependencies
ALL_PLSQL_OBJECT_SETTINGS DBA_WARNING_SETTINGS
ALL_WARNING_SETTINGS DBMS_PLSQL_WARNING_LIB
DBA_PLSQL_OBJECT_SETTINGS USER_WARNING_SETTINGS
Initialization Parameters PLSQL_WARNINGS
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%';
Security Model Execute is granted to PUBLIC. 

SELECT on the view dba_warning_settings is granted to the  select_catalog_role role.
 
 
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 (function)
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 (procedure)
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;

 
Related Topics
DBMS_WARNING_INTERNAL
Errors
Exception Handling
PL/SQL Object Settings
PL/SQL Warnings
Procedures
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [252 users online]    © 2010 psoug.org