RE: Selecting into Var with no recs returned

From: Andor, Gyula <gy.andor_at_euromacc.hu>
Date: Fri, 27 Apr 2001 08:29:12 -0700
Message-ID: <F001.002F438F.20010427080032@fatcity.com>

Try This. In this example you can handle the exception without exiting from the procedure. The exception interrupts the process between the inner begin-end block. The execution is continued after the inner begin-end block.

Procedure Something as
Begin

 .
 .
 .
 .
         Begin
             SELECT my_id INTO v_temp
             FROM my_tablee
             WHERE my_id = v_number;
          Exception
                When NO_DATA_FOUND THEN DOSOMETHING;
         End;
.
.

.
.
End;

> In my Oracle 8.1.7 stored procedure, I'm selecting into a
> variable and need
> to check to see if anything was found but I get an exception
> when there are
> no records. I guess I could select count() and check the
> returned count
> number but I thought there might be a better way. Any ideas?
>
> SELECT my_id INTO v_temp
> FROM my_tablee
> WHERE my_id = v_number;
> IF SQL%FOUND THEN
> p_error_code := 100;
> RETURN;
> END IF;
>
>
> TIA,
>
> Woody
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Andor, Gyula
  INET: gy.andor_at_euromacc.hu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Apr 27 2001 - 10:29:12 CDT