Oracle Tips and Tricks — David Fitzjarrell

June 26, 2012

Nullified Remains

Filed under: pl/sql — dfitzjarrell @ 18:58
Tags: , ,

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.

About these ads

1 Comment »

  1. Absolutely reprehensible! :)

    The WHEN OTHERS THEN NULL gets added most often by a developer when the code is raising an exception that they want to ignore. Instead of adding a handler specifically for that exception, they just use WHEN OTHERS; the code compiles, the unwanted exception goes away; it passes all the tests, so they think “job’s done”.

    Even worse, I’ve seen it added as a bug fix in production code; and it might be weeks or months before someone notices the data corruption.

    Comment by Jeffrey Kemp — November 12, 2012 @ 16:35 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 629 other followers

%d bloggers like this: