100 - [Oracle PL/SQL] Compiler - package dbms_warning

 

 

 

 

 

 

 

 

 

ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';

col name for a30
col value for a30

select name,value 
from v$parameter
where name='plsql_warnings';
---------------------------------------------
NAME                           VALUE                         
------------------------------ ------------------------------
plsql_warnings                 DISABLE:ALL

 

 

• 경고 메시지 표시형식 설정

--1--
--Modify the current session''s warning settings
/*
dbms_warning.add_warning_setting_cat(
 warning_category  IN VARCHAR2,
 warning_value    IN VARCHAR2,
 scope            IN VARCHAR2)
*/

--now we can modify the setting
begin
    dbms_warning.add_warning_setting_cat('SEVERE', 'ENABLE', 'SESSION');
end;


--we can check the new value by
select name,value 
from v$parameter
where name='plsql_warnings'
------------------------------------------------------------
NAME                           VALUE                         
------------------------------ ------------------------------
plsql_warnings                 DISABLE:INFORMATIONAL, DISABLE
                               :PERFORMANCE, ENABLE:SEVERE 
--or 
SELECT dbms_warning.get_warning_setting_string FROM dual;
------------------------------------------------------------
GET_WARNING_SETTING_STRING                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
----------------------------------------------------------
DISABLE:INFORMATIONAL,DISABLE:PERFORMANCE,ENABLE:SEVERE

 

 

 

• 경고메시지 번호별 활성/비활성 설정 방법

--2, 경고메시지 번호별 활성/비활성 설정 방법
/*
dbms_warning.add_warning_setting_num(
 warning_number  IN PLS_INTEGER,
 warning_value  IN VARCHAR2,
 scope          IN VARCHAR2);
*/

EXEC dbms_warning.add_warning_setting_num(6002, 'DISABLE', 'SESSION');

SELECT dbms_warning.get_warning_setting_num(6002)
 FROM dual;
---------------------------------------------------------
DBMS_WARNING.GET_WARNING_SETTING_NUM(6002)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
---------------------------------------------
DISABLE:  6002
 
EXEC dbms_warning.add_warning_setting_num(6002, 'ENABLE', 'SESSION');

SELECT dbms_warning.get_warning_setting_num(6002)
FROM dual;
---------------------------------------------------------
DBMS_WARNING.GET_WARNING_SETTING_NUM(6002)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
---------------------------------------------
ENABLE:  6002

 

 

 

• 경고 메시지 그룹 일괄 설정

--3. 경고 메시지 그룹 일괄 설정
/*
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;
---------------------------------------------------------
GET_WARNING_SETTING_STRING
---------------------------------
ENABLE:ALL

 

 

 

• 메시지 코드가 소속된 그룹명 표시

--4. 메시지 코드가 소속된 그룹명 표시
/*
dbms_warning.get_category(warning_number IN PLS_INTEGER)
 RETURN VARCHAR2

*/
-- severe
 SELECT dbms_warning.get_category(5000)
 FROM dual;
------------------------------------
DBMS_WARNING.GET_CATEGORY(5000)
------------------------------------
SEVERE

 -- informational
 SELECT dbms_warning.get_category(6002)
 FROM dual;
------------------------------------
DBMS_WARNING.GET_CATEGORY(6002)
-----------------------------------------
INFORMATIONAL

 -- performance
 SELECT dbms_warning.get_category(7203)
 FROM dual;
 ------------------------------------
DBMS_WARNING.GET_CATEGORY(7203)
-----------------------------------------
PERFORMANCE

 

 

 

• 그룹별 경고메시지 설정 여부 표시

 --5. 그룹별 경고메시지 설정 여부 표시
 /*
 dbms_warning.get_warning_setting_cat(warning_category IN VARCHAR2)
 RETURN VARCHAR2
*/

SELECT dbms_warning.get_warning_setting_cat('SEVERE')
FROM dual;
-------------------------------------------------
DBMS_WARNING.GET_WARNING_SETTING_CAT('SEVERE')
-------------------------------------------------
ENABLE:SEVERE


SELECT dbms_warning.get_warning_setting_cat('INFORMATIONAL')
FROM dual;
-------------------------------------------------
DBMS_WARNING.GET_WARNING_SETTING_CAT('INFORMATIONAL')
-------------------------------------------------
ENABLE:INFORMATIONAL


SELECT dbms_warning.get_warning_setting_cat('PERFORMANCE')
FROM dual;
-------------------------------------------------
DBMS_WARNING.GET_WARNING_SETTING_CAT('PERFORMANCE')
-------------------------------------------------
ENABLE:PERFORMANCE

 

 

 

--6. 특정 경고메시지의 활성화 여부 확인

--dbms_warning.get_warning_setting_num(warning_number IN PLS_INTEGER)
--RETURN VARCHAR2 

SELECT dbms_warning.get_warning_setting_num(5000)
FROM dual;
-------------------------------------------------
DBMS_WARNING.GET_WARNING_SETTING_NUM(5000)
-------------------------------------------------
ENABLE:  5000


SELECT dbms_warning.get_warning_setting_num(6002)
FROM dual;
-------------------------------------------------
DBMS_WARNING.GET_WARNING_SETTING_NUM(6002)
-------------------------------------------------
ENABLE:  6002


SELECT dbms_warning.get_warning_setting_num(7203)
FROM dual; 
-------------------------------------------------
DBMS_WARNING.GET_WARNING_SETTING_NUM(7203)
-------------------------------------------------
ENABLE:  7203