Re: SELECT INTO when there is no data.

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

That's correct no doubt, but when I use exceptions handling - I lose my workflow of the procedure. My procedure doesn't include only one SELECT. The procedure contains other things. In SQL Server there is something like IF EXISTS (select * from table) THEN ...
Is there something like that in Oracle ?

Thanks for all of you for your assistance,

   Moshe. Received on Wed Mar 17 2004 - 09:36:19 CET