Just recently a service request was opened with Oracle Support regarding the “when others then null” exception handler when it was found in an Oracle-supplied form for the Oracle Inventory application from the E-Business suite. It appears the person who opened the SR believes (and rightly so) this is not the most informative of exception handlers nor is it proper coding practice; the request was opened in hopes of disallowing such constructs. I originally posted:
“Sadly Oracle Support may not do anything about this for two reasons:
1) It’s valid code.
2) It’s not causing another error to surface.
Amending my post to reflect the actual nature of the SR it’s now clear why this was opened — Oracle should never issue forms or production code using this exception ‘non-handler’ so it’s understandable why Oracle Support filed a bug report (bug number 14237626) for it. Why is coding “when others then null” not the best idea? Let’s look at some examples and see what could be hidden behind that glorious construct. Creating a table used in a previous post:
SQL> -- SQL> -- Create a test table SQL> -- SQL> create table pktest( 2 pk number, 3 val1 number, 4 val2 number, 5 val3 number 6 ); Table created. SQL> SQL> -- SQL> -- Add the primary key SQL> -- SQL> alter table pktest add constraint pktestpk primary key (pk); Table altered. SQL> SQL> -- SQL> -- Insert data SQL> -- SQL> begin 2 for i in 1..25 loop 3 insert into pktest 4 values(i,i+1,i+2,i+3); 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed. SQL> SQL> -- SQL> -- Commit the data SQL> -- SQL> commit; Commit complete. SQL>
Using this table/key combination let’s try some transactions that will generate errors; we’ll hide them by using “when others then null”:
SQL> -- SQL> -- Try and insert an existing key SQL> -- value into the test table SQL> -- SQL> -- Intentionally obscure the original SQL> -- error SQL> -- SQL> begin 2 insert into pktest values (1,2, 3, 4); 3 exception 4 when others then 5 null; 6 end; 7 / PL/SQL procedure successfully completed. SQL>
An insert was intentionally attempted that violates the primary key constraint yet no error was thrown — how lovely. The worst part of this is the code is declared to have run successfully; it appears that the insert was executed without error (which we know is NOT true) so the end user who ran this glorious piece of code has no idea his or her insert failed. These, of course, are not the only errors that can be hidden from view as other, more insidious errors can be ignored:
SQL> SQL> -- SQL> -- Artificially generate some rather severe SQL> -- errors SQL> -- SQL> -- Ignore them in the exception handler SQL> -- SQL> declare 2 bad exception; 3 reallybad exception; 4 trulyreallybad exception; 5 6 pragma exception_init(bad, -43); -- remote operation failed 7 pragma exception_init(reallybad, -1019); -- unable to allocate memory on user side 8 pragma exception_init(trulyreallybad, -1034); -- Oracle not available 9 begin 10 begin 11 raise bad; 12 exception 13 when others then null; 14 end; 15 begin 16 raise reallybad; 17 exception 18 when others then null; 19 end; 20 begin 21 raise trulyreallybad; 22 exception 23 when others then null; 24 end; 25 end; 26 / PL/SQL procedure successfully completed. SQL>
Notice that even these errors were not reported, including the ‘Oracle not available’ error indicating connection to the database failed resulting in nothing being executed. Oracle reported that the PL/SQL ran successfully (meaning without error) all because of the ‘when others then null’ error mis-handler.
All of this transpires due to a lack of knowledge of what errors could be expected, a bout of laziness on the part of the developer or both — “Gee, I don’t know what other errors to expect but they can’t be serious so I’ll ignore them.” Any error is serious to the end user as it prevents work from completing, and if those errors are ignored and the code block execution looks successful then, when problems arise because of missing data, the issue is harder to troubleshoot. Absent an error message, no matter how trivial that error may seem to the developer, the end user has no indication that the insert/update/delete failed and has nothing to report to the help desk. Codng such mis-handlers also is a disservice to the developer/programmer as they won’t know what caused the problem any more than the user who ran the code. Not knowing the cause makes the solution that much more difficult to find. In that case everybody loses.
This is the third post on error handling and error reporting I’ve written, two on properly reporting error text so the end user can talk intelligently to the help desk on what went wrong, and this one, on making sure there IS an error message to report. All three should be read as a set (in my opinion) so that error messages are reported and the text that the end user sees provides useful information to the service desk personnel. This falls, really, on the developers and programmers as the error handlers they code directly affect what the end users see when errors arise, and the error messages should be clear enough for a non-technical Oracle user to describe to the help desk representatives with whom they speak. A little effort on the development side goes a long way in making troubleshooting easier if, or when, the time comes.
Third time is a charm.