Re: NO_DATA_FOUND EXCEPTION

In <1995May4.112449.751_at_newton.ccs.tuns.ca>, James Richard writes:
>In article <3nbutc$4sp_at_news.annex.com> sanctus2_at_annex.com () writes:
>>In PL/SQL, if I do a SELECT INTO or a FETCH that returns 0 records, the
>>NO_DATA_FOUND EXCEPTION is raised. That's all well and good but what do
>>I do with it? If I write code to only do something if I RETRIEVE
>>records, why can't I ignore the NO_DATA_FOUND? It ends up generating an
>>error in my trigger and rolls back my transaction.
>>
>>I want to be able to retrieve no records WITHOUT erroring out my trigger.
>>
>>I'd appreciate any help on this issue.
>>Thanx....
>>
>>Jack Schwartz
>>Systems Analyst
>>Great Western Bank
>
>How about doing
>
>SELECT COUNT (*) INTO COUNTER FROM TABLE
>FROM TABLE WHERE etc.
>
>first.
>
>IF COUNTER > 0 THEN process
>END IF;
>
>This will bypass the NO_DATA_FOUND Exception.
>
>James Richard (JRICHARD_at_TUNS.CA)
>"No Assembler Required!"

This will work, but it will cause two you to execute two select statements for each record you wish to retrieve: 1 for a count, 2 for the data. Just add the exception handler to your code and have it do nothing, for example:

  EXCEPTION
    WHEN NO_DATA_FOUND /* no data was found */

       THEN 
           NULL;  /* do nothing */

M.Landa Received on Thu May 04 1995 - 00:00:00 CEST