numeric overflow in 10g

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

Other related posts: