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