TOO_MANY_ROWS is promoted, but the variable still gets the value

Question

I just found that if you have a SELECT INTO that throws an TOO_MANY_ROWS exception, the variable is still assigned the value from the first record that the query receives. Is this expected behavior?

Here is my example:

for co in my_cursor loop
        l_sco_db_id := null;

    begin
      select db_id
        into l_sco_db_id
        from objects_tab
       where object_name  = co.object_name;

    exception
      when no_data_found then
        dbms_output.put_line('No objects_tab record found for Object ' || co.object_name);
      when too_many_rows then
        dbms_output.put_line('Multiple objects_tab records found for Object ' || co.object_name);
        l_sco_db_id := null;
    end;
end loop;

This is inside the loop, so I set it to null at the beginning to make sure it is empty, but I had to explicitly do it again in the WHEN TOO_MANY_ROWS exception, which I did not expect. None of my colleagues (at least those in immediate delay) expected the variable to matter.

Answer 1

, , , , . , , . , undefined, .

a select into

• no_data_found,
• , too_many_rows, .

, , . Oracle select into statement ,

PL/SQL TOO_MANY_ROWS INTO undefined.

, Oracle , , , - . , - .

, Jeff Kemp, , , ,

CREATE or replace PROCEDURE proc2 (v OUT NUMBER) IS
  l_v integer;
BEGIN
   SELECT 1 INTO l_v FROM all_objects;
EXCEPTION
   WHEN TOO_MANY_ROWS THEN
      dbms_output.put_line
         ('TOO MANY ROWS: v='
          || l_v);
      v := l_v;
END;
/

, .

DECLARE
   v NUMBER;
BEGIN
   proc2(v);
   dbms_output.put_line('AFTER: v=' || v);
END;
/

Answer 2

. ( no_data_found, two_many_rows), :

• . (AVERAGE, COUNT, MIN, MAX SUM) . WHERE , COUNT - "0". MIN MAX , .

• ROWNUM.