When WHEN OTHERS is Evil

From the Database Geek.

As regular readers of my blogs know, one of the things I am is a non-absolutist. I try to never say never or always. Take WHEN OTHERS. I have worked in places where using WHEN OTHERS was banned. I think that’s silly. Nothing wrong with a WHEN OTHERS that logs an error. Right?

We recently had a situation that did just that. The problem was that the exception handler logged the SQLCODE and the SQLERRM (no stack info). This is an old piece of code being updated for some new functionality. Not a complete re-write, just some changes.

The developer split one big procedure into several small procedures (that’s a good thing), wrapped it in the main procedure and added an exception handler. This main procedure is called from a web front end (c#/asp.net).

We’re good so far. They started having a weird “wired numeric overflow” on their pages. There was no oracle error code, just a very generic .Net code. Sometimes, instead of an error, we just got a “page not found 404” error. Several of us in the data architecture group took a look. We all sort of assumed that it was a driver error or, at least, something in the middle tier. Assuming is bad as it makes you not look as deep as you might should.

It was narrowed down to a date conversion issue (usually). The error would eventually resurface no matter what kind of changes were made. First lesson, just because an error was caused by something one time does not mean that is the cause every time.

One thing that jumped out at me was as me and my boss were playing with it (trying to debug middle tier errors is a pain), we started writing log messages. The log has a primary key and we accidentally wrote the same key twice. Did we get a unique constraint violation? Nope, we got a wire numeric overflow error. Hmmmmm.

Turns out that the procedure had several problems. The date conversion issue was just one of them. The reason we were not seeing the error (always) was that sqlerrm was being put into a VARCHAR2(300) field. Sometimes (404 error) sqlerrm fit in the 300 byte variable. Sometimes, it did not (overflow).

I still don’t know why we got a wire numeric overflow instead of an oracle 6502 but there you have it. The exception was raising an unhandled exception. And even when it handled it, it screwed up the app with a 404 error. How to fix? Increase the size of the variable (and the log table) to 512 and either re-raise the exception or send an intelligent exception (maybe a specific exception page with an error description and contact info).

What’s the lesson? WHEN OTHERS is not evil. It does need to be used appropriately. Logging errors is good, but read the docs and make sure your data can fit. At least substring when you are not sure.


You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

Comments are closed.