Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Avoiding PL/SQL * NO_DATA_FOUND exception
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...Received on Fri Jun 21 2002 - 13:12:41 CDT
> 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
> > >
> > >
>
>
>