TOO_MANY_ROWS exeption with ONE ROW-CodePudding

This is odd or I am too tired but I dont get it (but I guess it is obvious). Here is the code :

for myVariable1 in (select distinct id from myTable1)    
loop 
    begin
      select myColumn into AVariable from myTable2 where id = myVariable1.id
    exception
      When TOO_MANY_ROWS then
        dbms_output.put_line('TOO_MANY_ROWS for ' || myVariable1.id);
    end;
end loop;

Quite simple and every id selected in myTable2 triggers a Too_MANY_ROWS exception though it is not the case.

here is my log:

   TOO_MANY_ROWS for 7500123

   TOO_MANY_ROWS for 5900123

Here is the result of the queries :

     select myColumn from myTable2 where id = '7500123'

     1 row returned

     select myColumn from myTable2 where id = '5900123'

     1 row returned.

Can somebody explain it ?

CodePudding user response：

I think that the id field contains the blank spaces. For example:

with table1 as (
                select '7500123'  as id from dual union
                select '7500123 ' as id from dual              
               )
             select *
               from table1
              where id = '7500123';

ID      
--------
7500123

And then when you run the query control give:

select myColumn from myTable2 where trim(id) = '7500123';

2 row returned

Please me back if it is the cause.

Thank you

CodePudding user response：

Do you have an unique constraint on table2.id ? If not, it may be a problem inside the transaction where you execute the loop. If you insert by mistake another row with the same id, you get the exception, the transaction is rollbacked and then when checking the uniqueness outside of the procedure you don't see the problem anymore.