PLSQL Newbie Qn

From: simon <simon_at_whhost.com.au>
Date: Fri, 10 Jun 2005 23:40:12 +0800
Message-ID: <42a9b45d$1_2@rain.i-cable.com>

I am new to PLSQL. Is this a good programming practice to test if there is 0, 1 or > 1 rows returned from a SELECT INTO:

DECLARE
    v_number number;
BEGIN
    BEGIN
            SELECT id INTO v_number FROM table where col=something;     EXCEPTION

            WHEN NO_DATA_FOUND THEN
              v_number:=0;
            WHEN TOO_MANY_ROWS THEN
                v_number:=99; /*special value for >1 rows*/
      END;

    IF v_number =0 THEN
   ..../*no row*/
    ELSIF v_number=99 THEN
.../*>1 rows*/

    ELSE
...../* 1 row*/

    END IF; END; I have the feeling that exception should really means something abnormal - rather than a way test to test normal conditions. But, it seems that SELECT INTO has to generate exception if rows count is not 1. Received on Fri Jun 10 2005 - 10:40:12 CDT