Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Avoiding PL/SQL * NO_DATA_FOUND exception

Re: Avoiding PL/SQL * NO_DATA_FOUND exception

From: Vladimir M. Zakharychev <bob_at_dpsp-yes.com>
Date: Fri, 21 Jun 2002 22:12:41 +0400
Message-ID: <aevqb2$gvg$1@babylon.agtel.net>


One easy way to avoid NO_DATA_FOUND is to use an aggregation function (say, max()) on each column you select (you should be sure you are selecting zero or one row, otherwise your logic may spin into wrong direction) and test if they are NULL, which effectively means there was no data found. For example, assuming my_table has ID as primary key, the following code will not throw any exceptions:

if l_value is not null then
 --process your row here
end if;

In this particular case we are sure that our query will return at most one row, so we can safely use max() to avoid no_data_found. If your query can return more than one row, but it is not expected, max() will in fact mask the fact that the query returned more than one row, and may pick wrong value as result, while classic query will simply throw an exception in this case.

Anyway, it is better to enclose your queries you don't want any exceptions from into begin ... exception when no_data_found then... end; blocks and handle this particular exception the way you want. Do NOT use WHEN OTHERS THEN unless you are absolutely sure you can safely ignore ANY errors in a block as OTHERS will mask all errors and will make debugging very problematic if some unusual error occurs - you won't see it and will waste a lot of time tracing it to this exception handler. At least log all errors from others handler to some safe place (a file or a trace table in an autonomous transaction) for future review.

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Thomas Gaines" <Thomas.Gaines_at_noaa.gov> wrote in message news:3D134CEA.A6936B15_at_noaa.gov...

> Jon -
>
> Raphael is absolutely correct. Creating a subblock inside of your
> main PL/SQL block is tremendously easy, and it allows for
> clever suppression of errors that may arise.
>
> For an example of a PL/SQL "main" block that contains a
> subblock, refer to Oracle technical note 168492.1, "How
> to Force a PL/SQL Block to Continue Processing After
> Exception is Raised," or any good Oracle PL/SQL book.
> Chapter 8 of Steven Feuerstein's "Oracle PL/SQL
> Programming" comes to mind. The Oracle technical
> note is available at Metalink.
>
> Thanks,
> TG
>
> Raphael Ploix wrote:
>
> > Jon,
> >
> > just enclose your 'select into' code in an exception handler:
> >
> > begin
> > select ... into ...
> > exception
> > when no_data_found then
> > null; -- or whatever else!
> > end;
> >
> > "Jon" <jzuazoa_at_nexo.es> wrote in message
> > news:aeuu6t$shu$1_at_unbe.sarenet.es...
> > > Hi,
> > >
> > > Developing a PL/SQL stored procedure, I would to avoid NO_DATA_FOUND
> > > exception but without having to do a SELECT COUNT(*) previously as I am
> > > afraid that this is consumes lots of resources. Is there any other way to
> > do
> > > this task ?
> > >
> > > Thanks
> > >
> > >
>
>
>
Received on Fri Jun 21 2002 - 13:12:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US