RE: What are the largest and smallest NUMBER values?

"Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx> · Mon, 4 Apr 2005 08:40:21 +0100

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxxx

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@xxxxxxxxxxx and include the word
"subscribe" in either the subject or body.

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

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

Other related posts: