, ,

Strange too_many_rows

понедельник, 22 ноября 2010 г. Оставить комментарий

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>

0 коммент. »

Оставьте Ваш комментарий