Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TOO_MANY_ROWS raised, but variable still gets a value

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.

like image 325
AndyDan Avatar asked Oct 19 '16 18:10

AndyDan


People also ask

What causes TOO_MANY_ROWS?

If more than one row is returned, the TOO_MANY_ROWS exception occurs. Listing A shows an example from Oracle's HR sample schema: There is more than one employee with the last name King, so the script fails. There are four ways to make sure your code is safe from this error. Select rows using the primary key.

In which situation TOO_MANY_ROWS named exception occurs?

The pre-defined exception TOO_MANY_ROWS is raised whenA cursor fetches value in a variable having incompatible data type. SELECT INTO statement returns more than one row.

How do I fix Ora 01422 exact fetch returns more than requested number of rows?

This is where the error is being thrown. When an ORA-01422 is triggered, your SELECT INTO statement is retrieving multiple rows of data or none at all. If it is returning multiple, the predefined exception TOO_MANY_ROWS will be raised, and for no returns the PL/SQL will raise NO_DATA_FOUND.

How do you handle too many rows exceptions in PL SQL?

Handle an exception by trapping it with a handler or propagating it to the calling environment. For example, if your SELECT statement returns more than one row, TimesTen returns an error (exception) at runtime. As the following example shows, you would see TimesTen error 8507, then the associated ORA error message.


1 Answers

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;
/
like image 75
Justin Cave Avatar answered Sep 22 '22 13:09

Justin Cave