Hi Scott,

In terms of where you locate exception handling this one is often causes heated discussions, as a rule I tend to use the following simple ways to handle this.

1, if you can resolve the problem in a sub-area of a program (begin / end block) do it there.

2, if you can resolve the problem within a function / procedure do it at the end, and possibly re-run the block

otherwise

3, catch the exception in the top-most routine(s) handling different exceptions as needed, then have a one-off catch all exception.

4, use exceptions to pass back error conditions and not return values; people can and do forget to check these, so that bad things can be allowed to pass unnoticed.

Where your code is going to fire its own exceptions, define these and where you need to raise them do so and pass them back out.

Hope that this makes sense. It is a matter of style / preference.

Cheers

Keith


-----Original Message-----
From: Adams Scott D. - G6
[mailto:oracledba-ezmlmshield-x9901839.[Email address protected]
Sent: Wednesday, 2 November 2005 4:23 AM
To: LazyDBA Discussion
Subject: RE: PL/SQL Exception


I have that at the bottom of my procedure now. I was just reading the docs
and it mentioned enclosing the portion of the procedure that may cause that
error inside its own begin/end. Does that make sense?

Scott D. Adams
Database Administrator
FORSCOM, G6
scott.[Email address protected]
(404) 464-5833
DSN 367-5833

-----Original Message-----
From: Justin Cave (DDBC)
[mailto:oracledba-ezmlmshield-x94600397.[Email address protected]
Sent: Tuesday, November 01, 2005 12:19 PM
To: LazyDBA Discussion
Subject: RE: PL/SQL Exception

BEGIN
SELECT ... INTO ...
EXCEPTION
WHEN no_data_found THEN
NULL;
END;

Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

-----Original Message-----
From: Adams Scott D. - G6
[mailto:oracledba-ezmlmshield-x5634318.[Email address protected]
Sent: Tuesday, November 01, 2005 12:15 PM
To: LazyDBA Discussion
Subject: PL/SQL Exception

I have a procedure that has a section that could possibly return no data
found error. This is ok and expected. I would like to handle this in
the
exception portion of my procedure. I would like it to ignore that error
and
continue with the procedure. Any help would be greatly appreciated.



Scott D. Adams

Database Administrator





Oracle LazyDBA home page