Re: Different behaviour of TOO_MANY_ROWS in 8i and 9i

On 13 Jan 2005 08:17:55 -0800, spam547834521954673456_at_gmx.de (Holger Linge) wrote:

>Hi
>
>Just by chance, i stumbled about the following: I executed the
>following block on two different Servers, one is v8.1.7.4.0, the other
>v9.2.0.4.0.
>
>declare
> x number := NULL;
>begin
> select w into x from (select 9 w from dual union all select 3 from
>dual) a;
>exception
> when others then
> dbms_output.put_line('EXCEPTION: ' || x);
>end;
>
>On 8i, the result is:
>
>EXCEPTION:
>
>as expected (well, by me...)
>
>On 9i, it's:
>
>EXCEPTION: 9
>
>Looks like 9i fetches the first record and then raises TOO_MANY_ROWS,
>while 8i fetches nothing at all.
>
>Can someone give me some insights on this one?
>
>cu
>Holger

The only insight I can give you : Don't ever count on the content of x in the above situation. And don't even bother. No matter what version of Oracle.

When an exception is raised, the content of your variable x is unpredictable. Oracle might put 3, 9 or even 3.1415 into it (or just keep it NULL). Basicly, it's like the classical division-by-zero problem :

c := null;
a := 0;
c := 3/a;

This raises an exception, of course, and you won't bother what Oracle has put into c here, because nothing of it makes sense. You just handle the exception and continue work.

• Kenneth Koenraadt