Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: What are the largest and smallest NUMBER values?
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.
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);
The results I get are:
1.000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E-127
9.999999999999999999999999999999999999900000000000000000000000000000000000000000000000000000000E+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
Received on Mon Apr 04 2005 - 03:44:13 CDT