Oracle编译时警告

Oracle 10g开始可以启用编译时警告来发现程序运行时异常。这些警告在编译时不足以抛出异常，但是却会在运行时造成错误或低性能。

Compiler Warnings 编译器警告

Oracle 10g allows you to enable compile-time warnings that are useful to identify potential run-time problems in your programs. These warnings are not serious enough to raise an exception at compile time, but may cause run-time errors or poor performance.
To enable these warnings globally for your database, the administrator needs to set the database initialization parameter plsql_warnings either in the parameter file or dynamically with an ALTER SYSTEM SET PLSQL_WARNINGS statement. To enable warnings in only your session, use an ALTER SESSION SET PLSQL_WARNINGS statement. The setting string is a comma delimited list of settings.

Oracle 10g开始可以启用编译时警告来发现程序运行时异常。这些警告在编译时不足以抛出异常，但是却会在运行时造成错误或低性能。
需要DBA全局开启编译时警告时，可在参数文件指定参数plsql_warnings或者通过ALTER SYSTEM SET PLSQL_WARNINGS语句动态设置。
如果只是在会话中启用可使用ALTER SESSION SET PLSQL_WARNINGS语句。
该设置由逗号分隔。

The syntax for each setting is:
语法如下：

'[ENABLE | DISABLE | ERROR]:[ALL | SEVERE | INFORMATIONAL | PERFORMANCE | warning_number]'
To enable all warning messages execute:

例如：

ALTER SYSTEM SET plsql_warnings = 'enable:all';
To enable all severe and performance messages execute: --启用所有严重和性能警告

ALTER SYSTEM SET plsql_warnings = 'enable:severe'
,'enable:performance';
To enable all warning messages except message 06002, execute: --启用除了06002以外所有警告信息

ALTER SYSTEM SET plsql_warnings = 'enable:all'
,'disable:06002';
Alternatively, you can use the built-in package dbms_warnings to set or view your warning setting. For example, to see what the current setting is, execute:

可选的，你可以使用内置包dbms_warnings来设置或者查看警告设置。例如：查看当前警告设置
BEGIN
DBMS_OUTPUT.PUT_LINE(DBMS_WARNING.GET_WARNING_SETTING_STRING());
END;
/

DISABLE:ALL
--默认设置

The warning error codes all begin with a ‘PLW-‘. The SEVERE errors are in the range 05000 to 05999. The INFORMATIONAL errors are in the range 06000 to 06999. The PERFORMANCE errors are in the range 07000 to 07249. On UNIX systems, a text file with the all of error codes, together with their cause and action can be found in $ORACLE_HOME/plsql/mesg/plwus.msg or a similar filename (plw??.msg) if the locale is not US.
警告错误代码以'PLW-'开头；
严重错误代码范围：05000到05999;
报告错误代码范围：06000到06999;
性能错误代码范围：07000到07249;
UNIX系统中在以下文件中包含所有错误代号以及原因和处理方法。$ORACLE_HOME/plsql/mesg/plwus.msg

An example of compiler warnings appears below:
来看两个出现编译警告的例子：

--1 不作用的代码（dead code）：
SQL> CREATE OR REPLACE PROCEDURE dead_code IS
2 x NUMBER := 10;
3 BEGIN
4 IF x = 10 THEN -- always TRUE
5 x := 20;
6 ELSE
7 x := 100; -- dead code
8 END IF;
9 END dead_code;
10 /

SP2-0804: Procedure created with compilation warnings

SQL> show errors
Errors for PROCEDURE DEAD_CODE:

LINE/COL ERROR
-------- -----------------------------------------------
7/7 PLW-06002: Unreachable code

--2 如何加固我们的函数返回值逻辑
说明：结构化编程应始终做到：One way in, one way out. 不要试图到处挖坑，最后坑的是自己。

考虑以下代码：
CREATE OR REPLACE FUNCTION status_desc (
cd_in IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
IF cd_in = 'C'
THEN RETURN 'CLOSED';
ELSIF cd_in = 'O'
THEN RETURN 'OPEN';
ELSIF cd_in = 'A'
THEN RETURN 'ACTIVE';
ELSIF cd_in = 'I'
THEN RETURN 'INACTIVE';
END IF;
END;

编译是无告警，表面看也没啥大问题是吧？那我执行以下语句呢？
BEGIN
DBMS_OUTPUT.PUT_LINE (status_desc ('X'));
END;
/

ORA-06503: PL/SQL: Function returned without value

问题还不小呢！! 下面启用编译时警告来提早发现问题！

ALTER SESSION SET plsql_warnings ='ENABLE:5005'
/

ALTER FUNCTION status_desc COMPILE
/

PLW-05005: subprogram STATUS_DESC returns without value at line 15

警告是有了，但是函数依然可以被执行！那怎么行，这是有问题的程序！下面启用更严格的警告阻止程序编译成功！
ALTER SESSION SET plsql_warnings ='ERROR:5005'
/

ALTER FUNCTION status_desc COMPILE
/

PLS-05005: subprogram STATUS_DESC returns without value at line 15

这下OK了，程序编译返回了严重警告代码PLS-05005，无法编译通过了！
接下来要干的就是修复代码了。That's it!

本文永久更新链接地址：