Skip to content
Tags

TOO_MANY_ROWS side effect

March 10, 2008

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!

About these ads

From → PL/SQL

3 Comments
  1. Brad permalink

    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 permalink

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

    • Thanks Fernando, good to know!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 175 other followers

%d bloggers like this: