Re: SELECT INTO when there is no data.
Date: 17 Mar 2004 00:36:19 -0800
Message-ID: <616be6f6.0403170036.4bf66e3a_at_posting.google.com>
sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0403160456.fc5c8c1_at_posting.google.com>...
> 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