Automatically Detect Exception Handlers That ''Swallow Up'' Errors

View All Scripts Login and Run Script

Script Name Automatically Detect Exception Handlers That ''Swallow Up'' Errors
Description Use compile time warnings to be warned if the compiler has identified an exception handler that does not contain a RAISE statement or a call to RAISE_APPLICATION_ERROR.
Category PL/SQL General
Contributor Steven Feuerstein (Oracle)
Created Thursday March 17, 2016

Statement 1
Not only does this demonstrate a very cool feature of compile-time warnings (you can convert a warning into an error), but it also gets around a (current) limitation of LiveSQL: it is not yet displaying compile time warnings. Only compile failures. PLW-06009 warning is: "PLW-06009: procedure "string" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR" Cause: The OTHERS handler can exit without executing some form of RAISE or or a call to the standard procedure RAISE_APPLICATION_ERROR.
Force 6009 Warning as a Compile Error
```
ALTER SESSION SET plsql_warnings = 'Error:6009'
```
Statement processed.
Statement 2
A RETURN is Not a Re-raise - FAIL!
```
CREATE OR REPLACE FUNCTION plw6009 
   RETURN VARCHAR2 
AS 
BEGIN 
   RETURN 'abc'; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      RETURN 'abc'; 
END plw6009;
```
Error at line 7: PLS-06009: procedure "PLW6009" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

Statement 3

A Re-raise of the Current Exception - OK!

CREATE OR REPLACE FUNCTION plw6009 
   RETURN VARCHAR2 
AS 
BEGIN 
   RETURN 'abc'; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      RAISE; 
END plw6009;

Function created.

Statement 4

Raise a New Error - OK!

CREATE OR REPLACE FUNCTION plw6009 
   RETURN VARCHAR2 
AS 
BEGIN 
   RETURN 'abc'; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      RAISE_APPLICATION_ERROR (-20000, 'I am raising an exception!'); 
END plw6009;

Function created.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials