Oracle Scratchpad

May 13, 2011

Overflow

Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 6:20 pm BST May 13,2011

Here’s a little gem I hadn’t come across before (because I hadn’t read the upgrade manuals). Try running the following pl/sql block in 9i, and then 10g (or later):

declare
        v1      number(38);
begin
        v1 := 256*256*256*256;
        dbms_output.put_line(v1);
end;
/

In 9i the result is 4294967296; but for later versions the result is:


declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 4

It’s not a bug, it’s expected behaviour. The expression consists of integers only, so Oracle uses INTEGER arithmetic that limits the result to roughly 9 significant figures. If you want the block to work in newer versions of Oracle you have to add a decimal point to (at least) one of the operands to make Oracle use NUMBER arithmetic that takes it up to roughly 38 significant figures.

11 Comments »

  1. Interesting… some days ago I tried to do something like this:

    begin
    v:= 8 * 1024 * 1024 * 1024;
    end;

    and got that same “problem”. I didn’t have time to research why, but now the “mistery” is solved…

    Thanks

    Comment by Heitor Kirsten — May 13, 2011 @ 7:03 pm BST May 13,2011 | Reply

  2. Yeah, I guess that makes sense. Sort of like:

    DECLARE
    v_str varchar2(1);
    BEGIN
    v_str := ‘A’ || ‘B';
    END;
    /
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at line 4

    WHILE

    DECLARE
    v_str varchar2(1);
    BEGIN
    v_str := substr(‘A’ || ‘B’,1,1);
    END;
    /

    Does not produce an error.

    I guess the rational is that Oracle is trying to stay away from casting the operands of the expression based on the datatype of the result of the expression.

    At the same time, you could make the argument that Oracle has to cast each operand of the expression to something and he is choosing integer because of what the 4 strings looked like (256). So, at the time that Oracle is making the judgement call of what datatype to use, if the result is known to be a more generous datatype than what Oracle would choose, why not make the choice of what to cast to based on the operand datatype of the result?

    Oh well. Thanks for the diversion. Little tidbits like that are, as you put it, “Gems”.

    Comment by Galen Boyer — May 13, 2011 @ 7:18 pm BST May 13,2011 | Reply

  3. I ran into this issue with some internal code a few years ago and blogged about it on 10g upgrade gotcha’s. Ahh, the memories. Bring on 12c or whatever they will call it.

    http://piontekdd.blogspot.com/2008/07/10g-migration-ramification-part-1-ora.html

    Comment by Bradd Piontek — May 13, 2011 @ 7:59 pm BST May 13,2011 | Reply

  4. Funny how I had to get used to it when I moved to Teradata and here it is in Oracle… ;-)

    Cheers.

    Carlos.

    Comment by Carlos — May 16, 2011 @ 6:54 am BST May 16,2011 | Reply


  5. It’s not a bug, it’s expected behaviour. The expression consists of integers only, so Oracle uses INTEGER arithmetic that limits the result to roughly 9 significant figures. If you want the block to work in newer versions of Oracle you have to add a decimal point to (at least) one of the operands to make Oracle use NUMBER arithmetic that takes it up to roughly 38 significant figures.

    Hmm, so

    v1 := cast(256 as number)*256*256*256;

    should also work ?
    Let’s try:


    sokrates@11.2.0.2 > declare
    2 v1 number(38);
    3 begin
    4 v1 := cast(256 as number)*256*256*256;
    5 dbms_output.put_line(v1);
    6 end;
    7 /
    4294967296

    PL/SQL procedure successfully completed.

    yeah, great !

    now, let’s play a bit with the cast-operator and we get:


    sokrates@11.2.0.2 > declare
    2 v1 number(38);
    3 begin
    4 v1 := cast(256 as integer)*256*256*256;
    5 dbms_output.put_line(v1);
    6 end;
    7 /
    4294967296

    PL/SQL procedure successfully completed.

    oops – who can explain that ?
    looks like a bug again, why does the arithmetic work when I explicitly cast the implicit integer to an integer ??

    Comment by Sokrates — May 16, 2011 @ 7:03 am BST May 16,2011 | Reply

    • I think this only applies if all the values are literals. By introducing a function, you are causing the PL/SQL engine to use the floating-point arithmetic engine.
      1 declare
      2 v1 number;
      3 v2 integer := 256;
      4 begin
      5 v1 := cast(256 as varchar2)*256*256*256;
      6 — v1 := 256*256*256*256;
      7 dbms_output.put_line(v1);
      8* end;
      SQL> /
      4294967296

      PL/SQL procedure successfully completed.

      Sorry, it has been almost 2.5 years since I first blogged on this upgrade issue. I’ve only seen it happen when using literals.

      Comment by Bradd Piontek — May 16, 2011 @ 7:59 pm BST May 16,2011 | Reply

    • I think the problem here is the use of the work “integer” in “integer arithmetic”. It doesn’t relate to integers in pl/sql, which uses the term as a shorthand for number(*,0) or something similar. The type that does “integer arithmetic” is the pls_integer:

      
        1  declare
        2     v number(38);
        3     j pls_integer;
        4  begin
        5     j := 256;
        6     v := j * j * j * j;
        7* end;
      SQL> /
      declare
      *
      ERROR at line 1:
      ORA-01426: numeric overflow
      ORA-06512: at line 6
      

      Comment by Jonathan Lewis — May 17, 2011 @ 1:18 pm BST May 17,2011 | Reply

  6. [...] Jonathan Lewis is in habit of sharing the gems, diamonds and pearls, and yet again shares a little but cute gem. [...]

    Pingback by Log Buffer #220, A Carnival of the Vanities for DBAs | The Pythian Blog — May 16, 2011 @ 9:12 am BST May 16,2011 | Reply

  7. Even if it’s expected behaviour, it looks horrible to me.
    What about the “backward compatibility” concept?
    And above all, is there any *real* advantage?
    I think that extreme optimization should be knowingly applied by the programmer using an integer variable as destination.
    By the same logic, we should not be able to concatenate two CHAR(1) strings into a VARCHAR2(100).

    Moreover the error returned is misleading, it should state “integer overflow” not “numeric overflow”.

    As it is, it’s just crazy and quirky.

    Flavio

    Comment by Flavio Casetta — May 16, 2011 @ 9:43 am BST May 16,2011 | Reply

    • I should have included a comment that it might be expected by the developers, but it would be most unexpected to anyone whose code had been working for the last 10 years. It’s the sort of thing that merits a “notable change in behaviour” bulletin; mind you, if there was one I probably wouldn’t have noticed it.

      Comment by Jonathan Lewis — May 17, 2011 @ 1:20 pm BST May 17,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,257 other followers