WHEN handling exceptions, pay special attention to the use of when others then, because when others then will eat all the exceptions. If no information is thrown after when others then, that is, when others then null, this may greatly increase the difficulty of program troubleshooting in the future, because even if an error occurs, there is no prompt. Therefore, we need to pay special attention to the when others then. In addition to one manual search, we can also use the following command to check during compilation:
- Alter session set plsql_warnings ='Enable: all';
Test example:
- Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
- ConnectedAsXxpo
- SQL>SetServeroutput on
- SQL> ALTER SESSION SET plsql_warnings ='Enable: all';
- Session altered
- SQL> @"D:/test/xxpotest. plb";
- Package body created
- SQL> sho err;
- ErrorsForPackage body xxpo. XXPO_TEST_PKG:
- LINE/COL ERROR
- -------- Success ----------------------------------------------------------------------------------------------------------
- 88/13 PLW-05004: identifier LOGIsAlso declaredInSTANDARD orIsA SQL builtin
- 285/5 PLW-07203: parameter'X _ message'May benefit from use of the NOCOPY compiler hint
- 504/5 PLW-07203: parameter'X _ message'May benefit from use of the NOCOPY compiler hint
- 569/5 PLW-07203: parameter'X _ message'May benefit from use of the NOCOPY compiler hint
- 1008/14 PLW-07204: conversion away from column type may resultInSub-optimal query plan
- 1027/14 PLW-07204: conversion away from column type may resultInSub-optimal query plan
- 2027/12 PLW-07204: conversion away from column type may resultInSub-optimal query plan
- 1667/14 PLW-06009: procedure"SET_STG_TBL"OTHERS handler does not endInRAISE or RAISE_APPLICATION_ERROR
- 1716/14 PLW-06009: procedure"SET_STG_TBL"OTHERS handler does not endInRAISE or RAISE_APPLICATION_ERROR
- 1773/14 PLW-06009: procedure"SET_STG_TBL"OTHERS handler does not endInRAISE or RAISE_APPLICATION_ERROR
- 1755/14 PLW-06009: procedure"SET_STG_TBL"OTHERS handler does not endInRAISE or RAISE_APPLICATION_ERROR
- 1737/14 PLW-06009: procedure"SET_STG_TBL"OTHERS handler does not endInRAISE or RAISE_APPLICATION_ERROR
- 1789/10 PLW-06009: procedure"SET_STG_TBL"OTHERS handler does not endInRAISE or RAISE_APPLICATION_ERROR
- 2046/10 PLW-06009: procedure"MAIN"OTHERS handler does not endInRAISE or RAISE_APPLICATION_ERROR
- 1910/11 PLW-06002: Unreachable code
- SQL>
