FAQ
I'm trying to nail down the range of NUMBER values. The range I come
up with, based on a maximum precision of 38 and a scale range of -84
through 127, is illustrated by the following code block:

DECLARE

min_num NUMBER(38,127);
max_num NUMBER(38,-84);
BEGIN

/* 127 is largest scale, so begin with 1 and move

decimal point 127 places to the left. Easy. */
min_num := 1E-127;
DBMS_OUTPUT.PUT_LINE(min_num);

/* -84 is smallest scale value. Add 37 to normalize

the scientific-notation, and we get E+121. */

max_num := 9.9999999999999999999999999999999999999E+121;

DBMS_OUTPUT.PUT_LINE(max_num);
END;

/

The results I get are:

1.000000000000000000000000000000000000000000000000000000000000000000000000000000

000000000000000E-127

9.999999999999999999999999999999999999900000000000000000000000000000000000000000

000000000000000E+121

These results make sense to me. What troubles me is that the SQL
Reference gives 1.0E-130 as the low end (versus my 1.0E-127). The SQL
reference also gives "up to, but not including 1.0E126" as the high
end. It would seem that the manual is incorrect. On the other hand,
perhaps there is some subtle point that I am just not seeing. So I'm
throwing my question, and my code out to see whether anyone can spot a
flaw in my thinking here.

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article,
or send email to Oracle-article-request_at_gennick.com and
include the word "subscribe" in either the subject or body.

Search Discussions

  • Lex de Haan at Apr 4, 2005 at 3:44 am
    Jonathan,
    one thing is for sure: if you are looking for the range of NUMBER values, you
    should also consider negative values --
    because I am pretty sure the smallest one will be in that category :-)

    Oracle's way of storing NUMBER values uses the first byte for the sign and
    exponent. I'll save you the details, but that means that both for negative and
    for positive NUMBER values the maximum exponent value is 62. this is base 100,
    of course.
    In other words, you can use the remaining bytes to construct positive and
    negative numbers following this approach:
    a*100^62 + b*100^61 + c*100^60 + ... until you are out of digits. so if you are
    looking for the maximum value, try this series:
    select 99*power(100,62) + 99*power(100,61) + ... from dual;

    for negative values, the last byte is used to store an end tag 0x66 (or decimal
    102) so you loose one byte.

    You can use the dump function to see how Oracle stores the NUMBER values. One
    thing you should keep in mind: we think decimal, so we think in terms of 1E-127,
    but Oracle internally works in base 100 - not base 10.

    kind regards,

    Lex.


    Visit my website at http://www.naturaljoin.nl

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On
    Behalf Of Jonathan Gennick
    Sent: Monday, April 04, 2005 02:48
    To: Oracle-L (E-mail)
    Subject: What are the largest and smallest NUMBER values?

    I'm trying to nail down the range of NUMBER values. The range I come up with,
    based on a maximum precision of 38 and a scale range of -84 through 127, is
    illustrated by the following code block:

    DECLARE

    min_num NUMBER(38,127);
    max_num NUMBER(38,-84);
    BEGIN

    /* 127 is largest scale, so begin with 1 and move

    decimal point 127 places to the left. Easy. */
    min_num := 1E-127;
    DBMS_OUTPUT.PUT_LINE(min_num);

    /* -84 is smallest scale value. Add 37 to normalize

    the scientific-notation, and we get E+121. */

    max_num := 9.9999999999999999999999999999999999999E+121;

    DBMS_OUTPUT.PUT_LINE(max_num);
    END;

    /

    The results I get are:

    1.000000000000000000000000000000000000000000000000000000000000000000000000000000

    000000000000000E-127

    9.999999999999999999999999999999999999900000000000000000000000000000000000000000

    000000000000000E+121

    These results make sense to me. What troubles me is that the SQL Reference gives
    1.0E-130 as the low end (versus my 1.0E-127). The SQL reference also gives "up
    to, but not including 1.0E126" as the high end. It would seem that the manual is
    incorrect. On the other hand, perhaps there is some subtle point that I am just
    not seeing. So I'm throwing my question, and my code out to see whether anyone
    can spot a flaw in my thinking here.

    Best regards,

    Jonathan Gennick --- Brighten the corner where you are http://Gennick.com *
    906.387.1698 * mailto:jonathan_at_gennick.com

    Join the Oracle-article list and receive one article on Oracle technologies per
    month by email. To join, visit
    http://five.pairlist.net/mailman/listinfo/oracle-article,
    or send email to Oracle-article-request_at_gennick.com and include the word
    "subscribe" in either the subject or body.

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

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedApr 3, '05 at 9:51p
activeApr 4, '05 at 3:44a
posts2
users2
websiteoracle.com

2 users in discussion

Lex de Haan: 1 post Jonathan Gennick: 1 post

People

Translate

site design / logo © 2022 Grokbase