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!
19 November 2011 - 6:21 am
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!
EXECUTE IMMEDIATE ‘SELECT ‘||i_column_name||
‘ FROM ‘||i_table_name||
‘ WHERE ‘||i_column_name||
‘ = gen_SHA1(”’||i_data_str||”’)’
WHEN NO_DATA_FOUND THEN
WHEN TOO_MANY_ROWS THEN
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.
16 August 2012 - 7:13 pm
Thanks Fernando, good to know!