Re: Best practice to corrupt data

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 26 Sep 2006 15:53:00 -0700
Message-ID: <1159311181.68120@bubbleator.drizzle.com>

Mark D Powell wrote:
> nospam_at_gojko.com wrote:

>> A common practice for handling errors in PL/SQL procedures is to catch
>> all errors in the top-most database layer and convert them into error
>> codes and human readable messages for client applications. This
>> technique is a relict from the past and, in fact, a very bad practice
>> from today's perspective, since it can lead to data corruption. More on
>> http://www.orafaq.com/node/993 .
>>
>> Gojko Adzic
>> http://www.gojko.com

>
> Blog by Gojko recommends handling database exceptions in calling
> programs rather than in stored code in the database.
>
> My take: exceptions should be handled as close to where the exception
> occurs in the stored code as possible and then re-raised and handled
> again in the calling program.

I agree with one caveat.

If you expect developers and DBAs/Apps DBAs to be able to diagnose and fix problems you need to separate exceptions into two different categories. Those caused by user error ... trying to stuff a string into a date field and those that are related to application behaviour.

If you expect it to be fixed it needs to be logged in a database table along with relevant demographics such as user, date, calling proc, code where error was generated, error message, etc.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Tue Sep 26 2006 - 17:53:00 CDT