099 - [Oracle PL/SQL] Compiler - Warnings

 

 

 

 

SEVERE: Messages for conditions that may cause unexpected behavior or wrong results, such as aliasing problems

with parameters

PERFORMANCE: Messages for conditions that may cause performance problems, such as passing a VARCHAR2 value to a NUMBER column in an INSERT statement

INFORMATIONAL: Messages for conditions that do not have an effect on performance or correctness, but that you

may want to change to make the code more maintainable, such as unreachable code that can never be executed

 

 

 

 

 

• 현재의 plsql_warnings 값 확인

col name for a30
col value for a30

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



--2 dbms_warning.get_warning_setting_string()
declare
    s varchar2(100);
begin
    s:= dbms_warning.get_warning_setting_string();
    dbms_output.put_line(s);
end;
---------------------------------------------------------------
DISABLE:ALL

 

 

 

--now the status is plsql_warnings	DISABLE:ALL
--let us do this
--it will compile without any warning

drop PROCEDURE P11;
/
CREATE OR REPLACE PROCEDURE P11
(p OUT  VARCHAR2) 
IS
  BEGIN
    p := 'test';
END;


col name for a10
col type for a10
col PLSQL_OPTIMIZE_LEVEL for a10
col PLSQL_CODE_TYPE for a15
col PLSQL_WARNINGS for a20


--P11 객체에 대한 정보 조회 테이블, 모든 plsql 객체 정보 조회 가능
select name, type, PLSQL_OPTIMIZE_LEVEL as OPT_level, PLSQL_CODE_TYPE,PLSQL_WARNINGS
from user_plsql_object_settings
where name ='P11'
-------------------------------------------------------------
NAME       TYPE        OPT_LEVEL PLSQL_CODE_TYPE PLSQL_WARNINGS      
---------- ---------- ---------- --------------- --------------------
P11        PROCEDURE           2 INTERPRETED     DISABLE:ALL

 

 

 

--now see the warning, 경고 범위 설정
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
/*
-------------------------------------------------------------
NAME       TYPE        OPT_LEVEL PLSQL_CODE_TYPE PLSQL_WARNINGS      
---------- ---------- ---------- --------------- --------------------
P11        PROCEDURE           2 INTERPRETED     ENABLE:ALL 
*/


--프로시져 생성, 2개의 경고 메시지가 출력된다
CREATE OR REPLACE PROCEDURE P11
(p OUT  VARCHAR2) 
IS
BEGIN
    p := 'test';
END;
----------------------------
LINE/COL  ERROR
--------- -------------------------------------------------------------
1/1       PLW-05018: unit P11 omitted optional AUTHID clause; default value DEFINER used
2/2       PLW-07203: parameter 'P' may benefit from use of the NOCOPY compiler hint



-- 해당 경고에 대한 해결 방법은 아래와 같다
-- AUTHID definer         -- 해당 function을 만든 사용자 권한으로 실행한다는 의미
-- AUTHID current_user    -- 해당 function을 호출자(실행자) 권한으로 실행한다는 의미
CREATE OR REPLACE PROCEDURE P11
(p OUT NOCOPY VARCHAR2) AUTHID current_user
IS
BEGIN
    p := 'test';
END;

 

 

 

-- 경고메시지 다시 확인하는 방법
select * from user_errors
where name='P11'

 

 

 

• 경고 레벨을 모두 DISABLE 로 변경

alter PROCEDURE  P11 compile PLSQL_WARNINGS='DISABLE:ALL';


select name, type, PLSQL_OPTIMIZE_LEVEL as OPT_level, PLSQL_CODE_TYPE,PLSQL_WARNINGS
from user_plsql_object_settings
where name ='P11'
-------------------------------------------------------------
NAME       TYPE        OPT_LEVEL PLSQL_CODE_TYPE PLSQL_WARNINGS      
---------- ---------- ---------- --------------- --------------------
P11        PROCEDURE           2 INTERPRETED     DISABLE:ALL

 

 

 

• 경고 설정

 

• 예시

ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:INFORMATIONAL';

ALTER SESSION SET PLSQL_WARNINGS='DISABLE:INFORMATIONAL, 
                                  ENABLE:SEVERE,
                                  ENABLE:PERFORMANCE';

ALTER SESSION SET PLSQL_WARNINGS='DISABLE:INFORMATIONAL, 
                                  ENABLE:SEVERE,ENABLE:PERFORMANCE,
                                  ERROR:05018';

 

 

 

-- 모든 경고를 표시하게 설정
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

col name for a30
col value for a30

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


--리턴 이후에 있는 코드에 대한 경고 메시지 출력
create or replace function test_f
    return number
    authid current_user
is
    v number:=0;
begin
    return v;
    v:=v+1; --THIS CODE WILL NEVER BE EXECUTED
end;
-------------------------------------------
LINE/COL  ERROR
--------- -------------------------------------------------------------
8/5       PLW-06002: Unreachable code


--now when you do this, this mean SEVERE&PERFORMANCE also will be disabled
--because oracle understand that you need to do new setting, and disable is the default
--so SEVERE&PERFORMANCE also will be disabled

ALTER SESSION SET PLSQL_WARNINGS='DISABLE:INFORMATIONAL';

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


--각 경고 레벨별 설정 방법
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:INFORMATIONAL, ENABLE:SEVERE,ENABLE:PERFORMANCE';

select name,value 
from v$parameter
where name='plsql_warnings';
---------------------------------------------
NAME                           VALUE                         
------------------------------ ------------------------------
plsql_warnings                 DISABLE:INFORMATIONAL, 
                               ENABLE:PERFORMANCE, 
                               ENABLE:SEVERE 


-- 인포메이션 경고(6000번대)는 disable 되어 출력되지 않는다
create or replace function test_f
    return number
is
    v number:=0;
begin
    return v;
    v:=v+1;
end;
------
--Warning(1,1): PLW-05018
LINE/COL  ERROR
--------- -------------------------------------------------------------
1/1       PLW-05018: unit TEST_F omitted optional AUTHID clause; default value DEFINER used


--경고 메시지를 에러로 등록하는 방법
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:INFORMATIONAL, 
                                  ENABLE:SEVERE,ENABLE:PERFORMANCE,
                                  ERROR:05018';
select name,value 
from v$parameter
where name='plsql_warnings';
---------------------------------------------
NAME                           VALUE                         
------------------------------ ------------------------------
plsql_warnings                 DISABLE:INFORMATIONAL, 
                               ENABLE:PERFORMANCE, 
                               ENABLE:SEVERE, 
                               ERROR:  5018 


--경고메시지가 에러로 등록되어 컴파일이 되지 않는다
create or replace function test_f
return number
is
v number:=0;
begin
return v;
v:=v+1;

end;
---------------------------------------------
LINE/COL  ERROR
--------- -------------------------------------------------------------
1/1       PLS-05018: unit TEST_F omitted optional AUTHID clause; default value DEFINER used
Errors: check compiler log