RE: numeric overflow in 10g

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Fri, 24 Oct 2008 11:50:14 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E36056C2EF8@CWYMSX04.Corp.Acxiom.net>


Thanks Bradd. I should have said that some folks have filed this as a bug but it appears Oracle has closed them all.  

Dave    


Dave Herring, DBA | A c x i o m M I C S / C S O

630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax


From: Bradd Piontek [mailto:piontekdd_at_gmail.com] Sent: Friday, October 24, 2008 11:31 AM
To: Herring Dave - dherri
Cc: oracle-l_at_freelists.org
Subject: Re: numeric overflow in 10g  

Yes, that is one of the by-products of Oracle 10gR2 (maybe R1 as well). I've encountered it several times on 10.2.0.3. I wasn't aware it was a bug, but rather a feature change.

I blogged about it in July:
http://piontekdd.blogspot.com/2008/07/10g-migration-ramification-part-1- ora.html

Bradd Piontek
  "Next to doing a good job yourself,

        the greatest joy is in having someone 
        else do a first-class job under your  
        direction."
  • William Feather

On Fri, Oct 24, 2008 at 11:27 AM, Herring Dave - dherri < Dave.Herring_at_acxiom.com> wrote:

Has anyone run into this issue in 10g? We're running 10.2.0.4 on RHEL 4.x. We've hit something matching bug 3591135:

declare
  v_base number;
begin
  v_base:= 100000000 * 22;
  dbms_output.put_line ('Success');
end;
/

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

I change the literal "22" to either "21" or "22.0" and it works. According to what I've found on Metalink, Oracle doesn't view this as a bug:

The following has been added to the "Upgrading SQL*Plus Scripts and PL/SQL" section of Database Upgrade Guide:
.

Evaluation of Numeric Literals



Evaluation of numeric literals has changed in 10g such that at least one of the constants in a numeric computation with literals must be a decimal specified to the 10th place. This is because 10g uses INTEGER arithmetic (approximately 9 significant digits) for some expressions whereas Oracle 9i release 2 used NUMBER arithmetic (approximately 38 significant digits). Therefore, if you are dealing with results of greater than 9 significant digits, one of the literals should be in decimal format to prevent numeric overflow errors. For example, in release 10g the computation of v1 in the following example causes a numeric overflow error.
.

DECLARE
 v1 NUMBER(38);
BEGIN
 v1 := 256*256*256*256;
 DBMS_OUTPUT.PUT_LINE(v1);
END;
/
.

The solution to the error is to specify one of the numeric literals as a decimal (256.0), as follows:
.

DECLARE
 v1 NUMBER(38);
BEGIN
 v1 := 256*256*256*256.0;
 DBMS_OUTPUT.PUT_LINE(v1);
END; That's just great, but its happening with Affinium Campaign and they can't seem to reproduce the issue in their tests, even with our data and matching Oracle install. Plus I'd rather not rely on them.

Dave  



Dave Herring, DBA | A c x i o m M I C S / C S O 630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax

*
The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank you.



*
--
http://www.freelists.org/webpage/oracle-l



 




-- http://www.freelists.org/webpage/oracle-l

image001.gif
Received on Fri Oct 24 2008 - 11:50:14 CDT

Original text of this message