Re: help with select into

From: Karl E. Jørgensen <kjorg_at_msn*DOT*com>
Date: 1998/01/16
Message-ID: <u4ef56sI9GA.302@upnetnews04>#1/1

It doesn't make much sense to reference SQL%NOTFOUND after a SELECT ... INTO ... FROM... etc. For example:

    DECLARE
        myvar number;
    BEGIN

        SELECT some_column
        INTO my_variable
        FROM my_table
        WHERE ... whatever ...

        IF SQL%NOTFOUND THEN
            <NEVER-EXECUTED PL/SQL statements !!!!!>
        END IF;

    END; The reason is that Oracle treats it as an error if your query does not return any rows. Thus the select...into... WILL raise an exception (no_data_found). Without this, you would not know whether you got a value back or not !

There is two ways around this:

1. Encapsulate your SELECT ... INTO by BEGIN/END and catch the exception in the local block:

        BEGIN
            SELECT something
            INTO myvar
            FROM somewhere
            WHERE whatever;
        EXCEPTION WHEN No_Data_Found THEN
            myvar := acceptable_default_value;
        END;

2) Use a cursor:

        DECLARE
            CURSOR my_cursor is
                SELECT something
                FROM somewhere
                WHERE whatever;
        BEGIN
            OPEN my_cursor;
            FETCH my_cursor INTO myvar;
            IF my_cursor%NOTFOUND then
                myvar := acceptable_default_value;
            END IF;
            CLOSE my_cursor;
        END;

Hope this helps

--
Karl
Oracle Certified DBA

Sandy Thrasher wrote in message


>Here's a way to make sure the following SELECT in your example does not

>abort if no rows are found....

>

>>Select match_prod_id

>>into new_prod_id_out

>>from product_up_cross_sell

>>where product_up-cross_sell.base_prod_id = prod_id_in

>>and product_up_cross_sell.match_type = 'U'

>>

>IF SQL%NOTFOUND THEN

>      -- Do your exception handling here!

>ELSE

>     -- Other Stuff here!

>END IF;

>

>There is also SQL%FOUND or you could define an exception like below:

>

>BEGIN

>>Select match_prod_id

>>into new_prod_id_out

>>from product_up_cross_sell

>>where product_up-cross_sell.base_prod_id = prod_id_in

>>and product_up_cross_sell.match_type = 'U'

>>

>EXCEPTION

>   WHEN NO_DATA_FOUND THEN

>        -- Do  your exception stuff here!

>END;

>

>Sandy

>

>

>

Received on Fri Jan 16 1998 - 00:00:00 CST