TOO_MANY_ROWS and Variable Assignment

UPDATE : Also see Jeffrey Kemp's blog post on the same topic, earlier in 2008.

When you have a cursor that is assigning a return value to a variable, you expect the cursor to return a single row.  If it returns more than one row, the resulting error is a TOO_MANY_ROWS error.  This can be handled as an Exception.
However, this blog post is about what might be happening to the variable that you assign the fetched value to.

In this demo, I use a "CUSTOMER" table and a VALIDATE_CUSTOMER procedure :

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> 
SQL> drop table CUSTOMER purge;

Table dropped.

SQL> 
SQL> create table CUSTOMER
  2  (cust_id  number,  sales_mgr_id number, cust_name varchar2(50), cust_start_date date, status_flag varchar2(1));

Table created.

SQL> 
SQL> insert into CUSTOMER values (1,1,'USA-NY Customer',to_date('01-JAN-2010','DD-MON-YYYY'),'Y');

1 row created.

SQL> insert into CUSTOMER values (2,1,'USA-LA Customer',to_date('01-JUL-2010','DD-MON-YYYY'),'Y');

1 row created.

SQL> insert into CUSTOMER values (3,2,'GB-LN Customer',to_date('01-SEP-2010','DD-MON-YYYY'),'Y');

1 row created.

SQL> insert into CUSTOMER values (4,3,'FR-PR Customer',to_date('01-OCT-2010','DD-MON-YYYY'),'Y');

1 row created.

SQL> -- this cust_id 4 will be replacing the current one from 01-JAN-2013
SQL> -- unfortunately, the date entered is 01-JAN-2011
SQL> insert into CUSTOMER values (4,4,'JP-TY Customer',to_date('01-JAN-2011','DD-MON-YYYY'),'N');

1 row created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> 
SQL> create or replace procedure VALIDATE_CUSTOMER (in_cust_id in number, out_cust_status out varchar2)
  2  as
  3  -- initialize status
  4  -- U for Unknown
  5  l_cust_flag  varchar2(1) := 'U';
  6  begin
  7  select status_flag into l_cust_flag
  8  from  CUSTOMER
  9  where cust_id = in_cust_id
 10  and cust_start_date <= SYSDATE;
 11  out_cust_status := l_cust_flag;
 12  dbms_output.put_line('Flag : ' || l_cust_flag);
 13  dbms_output.put_line('Cust Status Returned : ' || out_cust_status);
 14  exception
 15  when others then
 16  dbms_output.put_line('Flag : ' || l_cust_flag);
 17  dbms_output.put_line('Exception  !! Cust Status Returned : ' || out_cust_status);
 18  end;
 19  /

Procedure created.

SQL> 
SQL> show errors
No errors.
SQL> 

So, I have a table with 2 rows for CUST_ID=4.  How will the procedure handle it ?
I have deliberately added dbms_output.put_line to show the values that the procedure "sees".

I run with three test CUST_IDs  : 1  (existant and valid),   9  (not existant),  4  ("too_many_rows").

SQL> SET SERVEROUTPUT ON
SQL> 
SQL> -- declare a variable to get the return value in sqlplus
SQL> 
SQL> -- a valid customer
SQL> variable cust_status_1 varchar2(1);
SQL> execute VALIDATE_CUSTOMER(1,:cust_status_1);
Flag : Y
Cust Status Returned : Y

PL/SQL procedure successfully completed.

SQL> print :cust_status_1

CUST_STATUS_1
--------------------------------
Y

SQL> 
SQL> -- a non-existent customer
SQL> variable cust_status_9 varchar2(1);
SQL> execute VALIDATE_CUSTOMER(9,:cust_status_9);
Flag : U
Exception  !! Cust Status Returned :

PL/SQL procedure successfully completed.

SQL> print :cust_status_9

CUST_STATUS_9
--------------------------------------------------------------------------------


SQL> 
SQL> -- a cust_id with too_many_rows
SQL> variable cust_status_4 varchar2(1);
SQL> execute VALIDATE_CUSTOMER(4,:cust_status_4);
Flag : Y
Exception  !! Cust Status Returned :

PL/SQL procedure successfully completed.

SQL> print :cust_status_4

CUST_STATUS_4
--------------------------------------------------------------------------------


SQL> 

The "Flag" and returned (output out_cust_status) values are correct for CUST_IDs 1 and 9. (For 9, the Flag is the initialized value of "U" because the cursor did not overwrite it).
However, for CUST_ID 4, although the procedure raises an EXCEPTION and returns no value, the Flag that is present *did* get reset from the initialized value of "U" to "Y" !! Apparently, when a cursor returns a row, it does assign the return value to the designated variable. If the cursor then returns *another* row, it raises an Exception but does not clear the value that was already assigned from the first row.

.
.
.