Re: SELECT INTO when there is no data.

Moshe_t_at_yahoo.com (moshe) wrote in message news:<616be6f6.0403170036.4bf66e3a_at_posting.google.com>...
> sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0403160456.fc5c8c1@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.

Why do you think it will not work? Why would you need anything else?

Read the manuals. You can nest Blocks so you can bring the exception clause as close to the Select as you want or need. So given two unrelated queries you might block them as

BEGIN
SELECT
EXECPTION
WHEN...
END
BEGIN
SELECT
EXECPTION
WHEN...
END while two related, dependent queries might be blocked as: BEGIN
SELECT
    BEGIN
    SELECT
    EXECPTION
    WHEN...
    END
EXECPTION
WHEN...
END IOW, what Sybrand told you is a very good suggestion. Learn how to use your tools properly, you'll be much happier.

HTH,
   ed
(trying to program ORACLE in the same manner as SYBASE is like trying to use a Phillips screwdriver as a wood chisel.) Received on Wed Mar 17 2004 - 20:49:01 CET