TOO_MANY_ROWS raised, but variable still gets a value

Question

I just discovered that if you have a SELECT INTO that raises a TOO_MANY_ROWS exception, the variable still gets assigned the value from the first record the query retrieved. Is that expected behavior?

Here's 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 a loop, so I set the variable null at the beginning to ensure it's blank, but I had to explicitly do it again in the WHEN TOO_MANY_ROWS exception, which I didn't expect. None of my coworkers (at least, those in immediate earshot) expected the variable to have a value, either.

Answer 1

It's expected behavior in that when you understand what is going on under the covers it makes some sense. But it's definitely a behavior that seems rather odd when you see it the first time. Technically, the behavior is documented to be undefined so it shouldn't be relied upon and could change in the future.

Under the covers, a select into is just syntactic sugar for

• Open cursor
• Fetch a row from the cursor into the target variable
• Throw a no_data_found exception if no row was fetched
• Attempt to fetch a second row from the cursor, throw a too_many_rows exception if that second fetch succeeded.

Given that, it sort of makes sense that the target variable would get written by the first fetch. The Oracle documentation for the select into statement, however, states that

PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined.

So Oracle is free to either leave the value unchanged or to let the variable have the value of the first row fetched or the second row or, realistically, anything else. And you shouldn't write code that depends on any particular behavior.

As an example, if you look at this blog post from Jeff Kemp, the variable takes on the value from the first row that was fetched. But if you make a small tweak to Jeff's code so that you fetch into a local variable

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;
/

then the behavior changes and the value doesn't appear to be overwritten.

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

Answer 2

Good observation. However, We can avoid exception handling (both no_data_found and two_many_rows) completely by following these tips:

• Select aggregate functions. By definition, the aggregate functions (AVERAGE, COUNT, MIN, MAX, and SUM) return only one row as a result. Even if its WHERE clause has no matching rows, a COUNT of those rows will return one row — the answer "0." Selecting the MIN or MAX of a table is frequently done in this way to determine processing limits.

• Limit the query using ROWNUM.