Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Avoiding PL/SQL * NO_DATA_FOUND exception

Re: Avoiding PL/SQL * NO_DATA_FOUND exception

From: Rauf Sarwar <rsarwar_at_ifsna.com>
Date: 21 Jun 2002 23:16:55 -0700
Message-ID: <c2d690f2.0206212216.1fb80b46@posting.google.com>


"Jon" <jzuazoa_at_nexo.es> wrote in message news:<aeuu6t$shu$1_at_unbe.sarenet.es>...
> Hi,
>
> Developing a PL/SQL stored procedure, I would to avoid NO_DATA_FOUND
> exception but without having to do a SELECT COUNT(*) previously as I am
> afraid that this is consumes lots of resources. Is there any other way to do
> this task ?
>
> Thanks

All good suggestions. Here is another one. Try using a cursor and use %NOTFOUND to determine if any rows were returned. No need for Exception handling. Only caveat with SELECT INTO... in procedures is that if multiple rows are returned, you will get an Exception. Using cursors can easily bypass that.

DECLARE
   dummy_ <DataType>;
   CURSOR cur_ IS

      SELECT whatever FROM wherever;
BEGIN
   OPEN cur_;
   FETCH cur_ INTO dummy_;
   IF (cur_%NOTFOUND) THEN

      ....
   ELSE
      ....
   END IF;
   CLOSE cur_;
END;
/ Received on Sat Jun 22 2002 - 01:16:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US