Re: SELECT INTO Return > 1 rows

Hi,

Looking at the code that you have supplied, I do not think that "ELSIF SQL%ROWCOUNT = 0 THEN" will also be executed ever. This is because you will get a No_data_found or Too_many_rows exception that needs to be trapped in the exception block. So in nut shell if the SELECT statment is not giving any rows or giving more than one row , the code will nto be able to handle it. A much better way to print the messages will be as stated under.

DECLARE
   v_city zipcode.city%type;
BEGIN
   SELECT city

     INTO v_city
     FROM zipcode
    WHERE zip = 07002;
     DBMS_OUTPUT.PUT_LINE(v_city ||' has a '||    'zipcode of 07002');

EXCEPTION
When Too_Many_Rows then

      DBMS_OUTPUT.PUT_LINE('error > 1 rows'); /*would this be executed at all?*/
When No_Data_Found then

      DBMS_OUTPUT.PUT_LINE('The zipcode 07002 is '|| ' not in the database');
When OTHERS then

      DBMS_OUTPUT.PUT_LINE('Some other erorr'); END;
/ Received on Fri Jun 10 2005 - 01:31:25 CDT