TOO_MANY_ROWS side effect

I used to assume that whenever a TOO_MANY_ROWS exception is raised, the target bind variables would be left untouched. Until today I’ve never written any code that relies on the bind variables being in any particular state when a TMR exception is raised, so was surprised.

For example, given the code below, I would expect the dbms_output to indicate that v is null:

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

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

TOO MANY ROWS: v=1
AFTER: v=1

What appears to happen is that the out bind variables will be assigned values from the first row returned from the query; then when a second row is found, the TOO_MANY_ROWS exception is raised.

According to the documentation (emphasis added):

“By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row.”

(Oracle Database PL/SQL User’s Guide and Reference (10gR2): SELECT INTO Statement)

So it appears my original stance (don’t assume anything about the variables’ state after TOO_MANY_ROWS is raised) was correct. Lesson learned: beware of performing a SELECT INTO directly on the OUT parameters of your procedure!

LIKE with wildcard (%) at start CAN use an index
My APEX application asks users to log in twice

Comments

  1. I found the following in some code (below) that contractors wrote for us. Thank for the tip on the side effect. Was very confusing why the variable v_hash_key was populated. We of course changed this to SELECT DISTINCT and removed the TOO_MANY_ROWS catch. I guess some contractors that make Business Machines don’t actually know it all!

    BEGIN
    EXECUTE IMMEDIATE ‘SELECT ‘||i_column_name||
    ‘ FROM ‘||i_table_name||
    ‘ WHERE ‘||i_column_name||
    ‘ = gen_SHA1(”’||i_data_str||”’)’
    INTO v_hash_key;

    RETURN v_hash_key;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    RETURN NULL;
    WHEN TOO_MANY_ROWS THEN
    RETURN v_hash_key;
    END;

  2. Fernando Fontana
    16 August 2012 - 3:44 am

    Oracle Forms behaves differently. It does not asign the variables of the INTO clause when a too_many_rows arises.

Leave a Reply

Your email address will not be published / Required fields are marked *