7. Compile-time warnings help avoid "WHEN OTHERS THEN NULL".
One of Tom Kyte's favorite pet peeves, the following exception sections "swallow up" errors.
EXCEPTION
WHEN OTHERS
THEN
NULL;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM);
In fact, any exception handler that does not re-raise the same exception or another, runs the risk of hiding errors from the calling subprogram, your users, and yourself as you debug your code.
Generally, you should log the error, then re-raise it.
There are certainly some cases in which this advice does not hold (for example: a function that fetches a single row for a primary key. If there is no row for the key, it's not an application error, so just return NULL). In those cases, include a comment so that the person maintaining your code in the distant future knows that you weren't simply ignoring the Wisdom of the Kyte. Example:
EXCEPTION
WHEN OTHERS
THEN
/* No company or this ID, let calling subprogram decide what to do */
RETURN NULL;
One way to avoid this problem is to turn on compile-time warnings. Then when your program unit is compiled, you will be warned if the compiler has identified an exception handler that does not contain a RAISE statement or a call to RAISE_APPLICATION_ERROR.
Related blog post: http://stevenfeuersteinonplsql.blogsp...
========================================
Practically Perfect PL/SQL with Steven Feuerstein
Copyright © 2015 Oracle and/or its affiliates. Oracle is a registered trademark of Oracle and/or its affiliates. All rights reserved. Other names may be registered trademarks of their respective owners. Oracle disclaims any warranties or representations as to the accuracy or completeness of this recording, demonstration, and/or written materials (the “Materials”). The Materials are provided “as is” without any warranty of any kind, either express or implied, including without limitation warranties or merchantability, fitness for a particular purpose, and non-infringement.
Watch video 7. Compile-time warnings help avoid "WHEN OTHERS THEN NULL". online, duration hours minute second in high quality that is uploaded to the channel Practically Perfect PL/SQL with Steven Feuerstein 29 March 2016. Share the link to the video on social media so that your subscribers and friends will also watch this video. This video clip has been viewed 2,502 times and liked it 9 visitors.
