Oracle PL/SQL之EXCEPTION — WHEN OTHERS THEN

在處理EXCEPTION時應特別注意WHEN OTHERS THEN的使用，因為WHEN OTHERS THEN會吃掉所有的EXCEPTION。如果在WHEN OTHERS THEN後不拋出任何資訊，即：WHEN OTHERS THEN NULL; 這可能會極大地增加以後程式排錯的難度，因為即使出錯了，也沒有任何提示。所以我們需要特別留意WHEN OTHERS THEN，除了一個一個的手工尋找外，還可以採用如下命令以便在編譯時間就進行檢查：

ALTER SESSION SET plsql_warnings = 'enable:all';

 

測試範例：

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0<br />Connected as xxpo</p><p>SQL> set serveroutput on</p><p>SQL> ALTER SESSION SET plsql_warnings = 'enable:all';</p><p>Session altered</p><p>SQL> @"D:/test/xxpotest.plb";</p><p>Package body created</p><p>SQL> sho err;<br />Errors for PACKAGE BODY XXPO.XXPO_TEST_PKG:</p><p>LINE/COL ERROR<br />-------- --------------------------------------------------------------------------------------------------<br />88/13 PLW-05004: identifier LOG is also declared in STANDARD or is a SQL builtin<br />285/5 PLW-07203: parameter 'X_MESSAGE' may benefit from use of the NOCOPY compiler hint<br />504/5 PLW-07203: parameter 'X_MESSAGE' may benefit from use of the NOCOPY compiler hint<br />569/5 PLW-07203: parameter 'X_MESSAGE' may benefit from use of the NOCOPY compiler hint<br />1008/14 PLW-07204: conversion away from column type may result in sub-optimal query plan<br />1027/14 PLW-07204: conversion away from column type may result in sub-optimal query plan<br />2027/12 PLW-07204: conversion away from column type may result in sub-optimal query plan<br />1667/14 PLW-06009: procedure "SET_STG_TBL" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR<br />1716/14 PLW-06009: procedure "SET_STG_TBL" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR<br />1773/14 PLW-06009: procedure "SET_STG_TBL" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR<br />1755/14 PLW-06009: procedure "SET_STG_TBL" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR<br />1737/14 PLW-06009: procedure "SET_STG_TBL" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR<br />1789/10 PLW-06009: procedure "SET_STG_TBL" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR<br />2046/10 PLW-06009: procedure "MAIN" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR<br />1910/11 PLW-06002: Unreachable code</p><p>SQL>