Re:Selecting into Var with no recs returned

From: <dgoulet_at_vicr.com>
Date: Fri, 27 Apr 2001 10:34:37 -0700
Message-ID: <F001.002F4307.20010427073116@fatcity.com>

Woody,

That would be one way to handle it, but you can also try:

declare

    cursor my_cur is
    select my_id
    from my_tablee
    where my_id = v_number;
    v_temp mu_cur%rowtype;
begin

    open my_cur;
    fetch my_cur into v_temp;
    if(my_cur%found) then

whatever
elsif(my_cur%notfound) then

whatever2
end if;
end;
/

you could also use the NO_DATA_FOUND exception in an execption handler as in "exception when no_data_found then null;"

Dick Goulet

____________________Reply Separator____________________
Author: "Woody Mckay" <wmckay_at_hydrogenmedia.com>
Date:       4/27/2001 6:41 AM

Gurus,

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: Woody Mckay
INET: wmckay_at_hydrogenmedia.com

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------