Re: Simple question ...

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Fri, 06 Aug 2004 15:58:20 -0700
Message-ID: <1091833139.522962@yasure>

Ernest Morariu wrote:

> Daniel,
>
>

>>DECLARE
>>
>>x  VARCHAR2(20);
>>
>>BEGIN
>>    SELECT somecolumn
>>    INTO x
>>    FROM mytable
>>    WHERE 1=2;
>>
>>EXCEPTION
>>    WHEN NO_DATA_FOUND THEN
>>      x := 'No Data Found';
>>END;

>
>
> Thanks for the answer.
> Doing this way means that my procedure is based on handling the exception.
> The exception should be executed just for *exceptional* situation, not for
> verifying the existence of the data ...
>
>
> ernest

That is a matter of religion not science. Were that true there would no point in Oracle reserving the range -20000 to -20999 for application related errors.

In Oracle exception handling is defined not just by Oracle but also by application logic. If you prefer to handle it differently then try it this way.

DECLARE NoMatchingRecord EXCEPTION;
PRAGMA EXCEPTION_INIT(NoMatchingRecord, -01403);

BEGIN

     SELECT somecolumn
     INTO x
     FROM mytable
     WHERE 1=2;

EXCEPTION
     WHEN NoMatchingReocrd THEN
       x := NULL;

END; Does that make it better ... or just different?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri Aug 06 2004 - 17:58:20 CDT