Re: Exception handling inpl/sql

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Thu, 28 Aug 2003 09:34:27 -0700
Message-ID: <3F4E2F13.82DBEACD@exxesolutions.com>

Steve Holdoway wrote:

> Hi Folks,
>
> I'm trying to devise a scheme whereby I can retrospectively put some
> error handling into existing in-house developed code. In order to do
> this, I'm trying to use local procedures within the procedures
> themselves, thereby upsetting the existing code as little as possible.
> I'm trying to take the suggestions in Steven Feuerstein's articles on
> otn (managing exceptional behaviour) as a basis for this 'project'
>
> What I would like to do is to encapsulate all of the exception
> handling at the procedure level into a private procedure as well. Will
> this work, or will the exception handler just shoot off back to the
> calling routine?
>
> Cheers,
>
> Steve

I would suggest that you build an independent package with multiple procedures and functions, as required, to do your error handling. And set up the primary procedure with PRAGMA AUTONOMOUS_TRANSACTION so it acts independently of your existing code.

Then, in the existing blocks, add something like the following:

EXCEPTION
   WHEN OTHERS THEN

      ErrMsg := SQLERRM;
      error_package.error_handler('Unique Block Identifier', USER,

ErrMesg);

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Thu Aug 28 2003 - 11:34:27 CDT