Re: SELECT INTO when there is no data.

Moshe_t_at_yahoo.com (moshe) wrote in message news:<616be6f6.0403152300.65ecca17_at_posting.google.com>...
> Hi,
>
> I'm using the SELECT INTO syntax in my SP.
> Sometimes, the SELECT returns no records, and the SP raise exception
> for that reason.
>
> My solution : Firsly use SELECT COUNT(*) INTO NumOfRecords and then
> check
> the NumOfRecords, If it's greater/equal to 1 continue to the next
> SELECT INTO.
>
> I believe that there's another better solution for that situation. Any
> suggestion will be welcome.
>
> With thanks,
> Moshe.

Just trap the no_data_found exception
begin
<your select into>
exception
when no_data_found then <take appropiate action>; end;

Of course this is documented in the PL/SQL manuals. The other solution, not taking into account the NO_DATA_FOUND exception, would set the number of records to 0, if the TOO_MANY_ROWS exception occurs.
Consequently, it misses the point and it is incorrect.

Sybrand Bakker
Senior Oracle DBA Received on Tue Mar 16 2004 - 13:56:58 CET