ORA-20000

Accidentally discovered an unexpected result occurs when an error occurs too_many_rows.
I tested two versions of the DB 10.2.0.3.0 and 11.2.0.1.0. I wrote a few tests.

• Example 1.
  before=#
  after=#
  It's ok. Variable is not changed.
• Example 2.
  before=#
  after=EMP
  I think that this result is failed. If an error occurs, in this case too_many_rows, the variable should not be changed.
• Example 3
  before=#
  after=EXAMPLE
  As in the previous example, the result of an error.
• Example 4.
  before=#
  after=#
  All is well. The result is correct. In contrast to Example 3, there is an implicit conversion from number to string, ie function is applied to_char, which may lead to a correct result.
• Example 5.
  before=#
  after=#
  It's ok. In this example, we explicitly apply the function, leading to a correct result.
• Example 6.
  before=#
  after=too_many_rows
  The last example demonstrates how to write code.
  When error handling is necessary to assign a variable the value that is needed.

SQL> set serveroutput on
SQL> 
SQL> --Example 1. no_data_found
SQL> declare
  2    vdummy varchar2(2000) := '#';
  3  begin
  4    dbms_output.put_line('before=' || vdummy);
  5    begin
  6      select user_objects.object_name
  7      into   vdummy
  8      from   user_objects
  9      where  1 = 2;
 10    exception
 11      when no_data_found then
 12        null;
 13      when too_many_rows then
 14        null;
 15    end;
 16    dbms_output.put_line('after=' || vdummy);
 17  end;
 18  /
 
before=#
after=#
 
PL/SQL procedure successfully completed
SQL> --Example 2. to_many_rows and column of table
SQL> declare
  2    vdummy varchar2(2000) := '#';
  3  begin
  4    dbms_output.put_line('before=' || vdummy);
  5    begin
  6      select user_objects.object_name
  7      into   vdummy
  8      from   user_objects;
  9    exception
 10      when no_data_found then
 11        null;
 12      when too_many_rows then
 13        null;
 14    end;
 15    dbms_output.put_line('after=' || vdummy);
 16  end;
 17  /
 
before=#
after=EMP
 
PL/SQL procedure successfully completed
SQL> --Example 3. to_many_rows and custom string without cast
SQL> declare
  2    vdummy varchar2(2000) := '#';
  3  begin
  4    dbms_output.put_line('before=' || vdummy);
  5    begin
  6      select 'EXAMPLE'
  7      into   vdummy
  8      from   user_objects;
  9    exception
 10      when no_data_found then
 11        null;
 12      when too_many_rows then
 13        null;
 14    end;
 15    dbms_output.put_line('after=' || vdummy);
 16  end;
 17  /
 
before=#
after=EXAMPLE
 
PL/SQL procedure successfully completed
SQL> --Example 4. to_many_rows and custom number with cast to varchar2
SQL> declare
  2    vdummy varchar2(2000) := '#';
  3  begin
  4    dbms_output.put_line('before=' || vdummy);
  5    begin
  6      select 1
  7      into   vdummy
  8      from   user_objects;
  9    exception
 10      when no_data_found then
 11        null;
 12      when too_many_rows then
 13        null;
 14    end;
 15    dbms_output.put_line('after=' || vdummy);
 16  end;
 17  /
 
before=#
after=#
 
PL/SQL procedure successfully completed
SQL> --Example 5. to_many_rows and function
SQL> declare
  2    vdummy varchar2(2000) := '#';
  3  begin
  4    dbms_output.put_line('before=' || vdummy);
  5    begin
  6      select lpad(user_objects.object_name, 30, '@')
  7      into   vdummy
  8      from   user_objects;
  9    exception
 10      when no_data_found then
 11        null;
 12      when too_many_rows then
 13        null;
 14    end;
 15    dbms_output.put_line('after=' || vdummy);
 16  end;
 17  /
 
before=#
after=#
 
PL/SQL procedure successfully completed
SQL> --Example 6. right example
SQL> declare
  2    vdummy varchar2(2000) := '#';
  3  begin
  4    dbms_output.put_line('before=' || vdummy);
  5    begin
  6      select user_objects.object_name
  7      into   vdummy
  8      from   user_objects;
  9    exception
 10      when no_data_found then
 11       -- assign value
 12        vdummy := 'no_data_found';
 13      when too_many_rows then
 14        vdummy := 'too_many_rows';
 15    end;
 16    dbms_output.put_line('after=' || vdummy);
 17  end;
 18  /
 
before=#
after=too_many_rows
 
PL/SQL procedure successfully completed
 
SQL>