Oracle FAQ Your Portal to the Oracle Knowledge Grid
 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US

Home -> Community -> Mailing Lists -> Oracle-L -> RE: What are the largest and smallest NUMBER values?

# RE: What are the largest and smallest NUMBER values?

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Mon, 4 Apr 2005 08:40:21 +0100
Message-Id: <20050404074019.1CB698013206@smtp-out3.tiscali.nl>

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 [mailto: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 Received on Mon Apr 04 2005 - 03:44:13 CDT

Original text of this message

 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US