Doc ID 210869.1
Explanation
===========
The ‘~’ is the notation for positive infinity, which is usually the
result of an overflow. An Oracle number text conversion routine
outputs “~” or “-~” when the input number is positive or negative
infinity, respectively, or when rounding (due to buffer size limit)
causes overflow to +/- infinity.
Oracle stores numeric data in variable-length format. Each value is stored in
scientific notation, with one byte used to store the exponent and up to 20
bytes to store the mantissa. The resulting value is limited to 38 digits of
precision.
Zero and positive and negative infinity are stored using unique representations.
Zero and negative infinity each require one byte; positive infinity requires
two bytes.
If a positive value is extremely large and a numeric overflows occurs when
rounding a number, then the infinity sign (~) replaces the value.
Likewise, if a negative value is extremely small and a numeric overflow
occurs when rounding a number, then the negative infinity sign replaces
the value (-~).
Solution======== Concept of infinity is unsupported/undocumented in later Oracle versions.Oracle version 5 was the last release with documented support for positive and negative infinity. As mentioned above oracle can save numbers up to 38 digitsof precision, customers are advised not break this limit. Customers should take oracle’s 38 digit number precision into consideration whiledesigning their application and avoid numbers greater than this. If applicationsare designed to store numbers greater than 38 digits, oracle does not guaranteethe results. Example: Avoid using datatypes like “double.positive_infinity” in JAVA environment.
Incorrectly Stored Number Values or Data in a Database (Doc ID 311346.1)
Numbers in a FLOAT or DOUBLE Type Lose Precision (Doc ID 1429713.1)
An example of losing precision with too many digits:
50000001
In binary, that is:
(10111110101111000010000001)b
In “scientific” notation, that is:
(1.0111110101111000010000001)b * 2^25
The significand has room for only 23 bits. So the value stored is:
(1.01111101011110000100000)b * 2^25
The last two bits of the significand were lost.
When converting back to decimal, the missing bits are filled in with 0s:
(10111110101111000010000000)b
The result in decimal:
50000000
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5011470313887
and we said…
Floats are numbers.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( id number, code float );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t
2 values(
3
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
123456789012345678901234567890123456,
4
123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
123456789012345678901234567890123456)
5 /
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> set linesize 50
ops$tkyte@ORA817DEV.US.ORACLE.COM> set numwidth 50
ops$tkyte@ORA817DEV.US.ORACLE.COM> select id, code, id-code from t;
ID
————————————————–
CODE
————————————————–
ID-CODE
————————————————–
1.2345678901234567890123456789012345678900000E+125
1.2345678901234567890123456789012345679000000E+125
-1.0000000000000000000000000000000000000000000E+87
Doesn’t show that floats aren’t numbers, they are just a number with a different
precision then number in itself.
(it also shows that numbers in this particular case are capable of storing 39 digits —
this may vary a bit, 38 is all we assure you and that is what the float is actually
storing. The float is less precise in this case and in your case, not the other way
around).
In anycase, give me a REAL WORLD example where this difference makes a difference – for
example, are you storing numbers with 39 digits of precision (if you are BEWARE, 38 is
what we promise, not 39). If you are storing with 38, we can do. |
|
|
In SQL, the benefits of native operations don’t really show up unless you are using very specific functions – like LN(). for example select sum(float) vs select sum(number) will not be very different (but select sum(ln(float)) will outperform select sum(ln(number)) greatly)
In PLSQL, if you are doing lots of computations – you’ll find that floats/doubles outperform numbers – sometimes by a lot. But remember – lack of precision, inability to store certain numbers altogether make them useless for financial applications and the like. |
|
|
|
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1416003959639
Tom,
In working with my developers(Java), I have come across the topic of datatype conversion.
They have given me a script which creates tables using INTEGER and FLOAT as datatypes for
some of the columns. I questioned why these dayatypes were being used instead of NUMBER.
I was made aware of the fact that Oracle has its own proprietory datatypes. If the
application we are building wants to insert into or select from a column values which are
externally defined as FLOAT or INTEGER, should I create this column with the external or
internal datatype? And if I use an internal datatype, where does the conversion occur? I
have read through the Oracle documentation but still don’t quite understand. They are
also questioning how they/I can use external datatypes BIGINT, DOUBLE, and TIMESTAMP. As
always your words of wisdom are greatly appreciated.
Keith M Cutler
and we said…
All numbers in Oracle are stored as numbers. Float, integer, etc — they are all
synonyms for numbers.
tkyte@TKYTE816> create table t
2 ( a int, b float(5), c decimal(5,2),
d number(5), e number, f float );
Table created.
tkyte@TKYTE816> desc t
Name Null? Type
———————– ——– —————-
A NUMBER(38)
B NUMBER(5)
C NUMBER(5,2)
D NUMBER(5)
E NUMBER
F FLOAT(126)
We sometimes fake a float for you (but not ALWAYS — see above, b float(5)) in the data
dictionary views — if you look at the text of USER_TAB_COLUMNS, you would find:
decode(c.scale,
null, decode(c.precision#, null, ‘NUMBER’, ‘FLOAT’),
‘NUMBER’),
so, floats are fake, integers are fake — they are ALL numbers.
You, as a creator of tables, never need to worry about external datatypes — they are
only of concern to the developers.
They, as developers using JDBC, need not concern themselves either — they use JDBC and
it is all “hidden” underneath. They just use the type they want. Just make sure to use
a type that has enough precision to retain the number (eg: Oracle supports a number with
38 digits of precision, Java won’t hold a number with that much precision in an Int or
Double type — neither will C or Ada or any 3gl pretty much). |
|
|
what external loader tool? and have you asked them to correct their erroneous behaviour?
no, cast in a view won’t work. |
|
|
|
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1619552483055
Hi Tom,
I declared the datatype of a column of a table as NUMBER & Presumed it to be Number(38)
to be specific.
But What I found to be strange that, it is accepting digits beyong 38 digits.ie.
1.111E+125. If the Datatype’s precision is 38 and scale can range from -84 to 127 how
this value can insert into the column.
In one of the System owned tables, USER_TAB_PRIVS there is a column known as Table_Name
but under this column you will find all the objects of a schema which has been granted
object privs. whether it is table,view or any sequence.
Shouldn’t it be more precise to define this column name as Object_name rather that the
table_name.
Please do explain & clarify.
Thanks in Anticipation,
Vikas Khanna
Vkhanna@quark.stpm.soft.net
and we said…
1.111e+125 only has 4 digits of precision (number of digits needed to represent the
number — in this case 1111).
The 38 is for 38 digits of precision. The SCALE dictates how many places to the right or
left of the decimal place you may have.
It would be more clear to have USER_TAB_PRIVS say that — however, it would break lots of
stuff. User_tab_privs pre-dates many of these object types and retains its naming for
backwards compatibility reasons. |
|
|
I entered a number 12345678901234567890123456789012345678901234567890123456789012345678901234567890 into that particular column. It showed me the result as 12345678901234567890123456789012345678900000000000000000000000000000000000000000, when set numwidth 2000 was issued.(SQL Worksheet accepts it) When issued set numwidth 80 it displayed ,1.2345678901234567890123456789012345678900000000000000000000000000000000000000000E+79 Why it is so??Moreover I was not able to understand the concept behind this.Can u please explain in more detail how Oracle does store the Numbers internally. Take relevant examples for precision & scale.
Followup October 16, 2001 – 8am UTC:
Well first, I hope you have read the documentation (always a great place to start). see http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c10datyp.htm#743there is a nice table there with various precisions/scales that demonstrate the outcome. Numbers are stored with an assured (at least) 38 digits of precision. It is allowed to go higher (you got 39 digits of precision). Numbers that exceed the PRECISION when the precision is not specified are rounded to fit in there (just like if you stuff a 15 digit number into a C float/double — it’ll be made to “fit” since they hold numbers with 6/13 digits of precision only) The number is like number(*) (wildcard) ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x number(*) );Table created. ops$tkyte@ORA717DEV.US.ORACLE.COM> insert into t values ( 12345678901234567890123456789012345678901234567890 ); 1 row created. ops$tkyte@ORA717DEV.US.ORACLE.COM> select to_char(x,rpad(‘9′,60,’9’)) from t; TO_CHAR(X,RPAD(‘9′,60,’9’))————————————————————- 12345678901234567890123456789012345678900000000000 With a numwidth of 2000 — there was enough room to display the number in its entirety without using scientific notation — so it did. With a numwidth of 80, there was NOT enough room — so it used scientific notation to display the number in your requested field size. That part seems all very logical (to me anyway, it was just doing exactly what you asked it to do). |
|
|
|
Number or number(p)
Hi
What the difference between declaring a column using datatype
number
and
number(n)?
Ay advantage or disadvantge declaring one way and the another?
Followup June 30, 2003 – 7am UTC:
number(n) is a number with a constraint and edit.
ops$tkyte@ORA920> create table t ( x number, y number(5) );
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t values ( 123.456, 123.456 );
1 row created.
ops$tkyte@ORA920> insert into t values ( 123.999, 123.999 );
1 row created.
ops$tkyte@ORA920> insert into t values ( 12345, 12345 );
1 row created.
ops$tkyte@ORA920> insert into t values ( 123456, 123456 );
insert into t values ( 123456, 123456 )
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
ops$tkyte@ORA920> select * from t;
X Y
———- ———-
123.456 123
123.999 124
12345 12345
See how the number(5) behaves? 5 digits, no decimals, rounded.
The advantage is
a) you have 5 digits only, never more
b) you have no decimals, it is an integer type
consider it a constraint — like a primary key, NOT NULL, check, whatever.
If your data is such that the number should never be more then 5 digits, the only correct way to
implement it would be as a number(5) |
|
|
|
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1619552483055
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1619552483055
Followup December 1, 2009 – 2am UTC:
You don’t say what client you are using, but it looks like you are using some application that is using floating point numbers in the client to fetch into.
floating point numbers have 6 or 13 digits of precision (we have 38) and are incapable of holding many numbers without losing precision (eg: they cannot store most numbers correctly).
ops$tkyte%ORA10GR2> create table t (a number(20,10),b number(20,11),c number(20,12),d
number(20,13),e number(20,14),f number(20,15));
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t values (20991.06,20991.06,20991.06,20991.06,20991.06,20991.06);
1 row created.
ops$tkyte%ORA10GR2> insert into t values (400.85,400.85,400.85,400.85,400.85,400.85);
1 row created.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set numformat 99999.0000000000000009
ops$tkyte%ORA10GR2> select * from t;
A B C
———————– ———————– ———————–
D E F
———————– ———————– ———————–
20991.0600000000000000 20991.0600000000000000 20991.0600000000000000
20991.0600000000000000 20991.0600000000000000 20991.0600000000000000
400.8500000000000000 400.8500000000000000 400.8500000000000000
400.8500000000000000 400.8500000000000000 400.8500000000000000
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select cast( f as binary_double ) from t;
CAST(FASBINARY_DOUBLE)
———————–
20991.0600000000010000
400.8500000000000200
ops$tkyte%ORA10GR2> select cast( f as binary_float) from t;
CAST(FASBINARY_FLOAT)
———————–
20991.0605000000000000
400.8500060000000000
So, I think the problem is not in the database (use sqlplus and you’ll see) but in the client that fetches and displays this data – that client should not be used to modify any numeric data – you’ll end up messing it up. |
|
|
|
I have a simple doubt regarding number declaration. Number is declared as Number(p,s). The maximum
value precision can take is 38 and the least value of scale is -84. But declaring any number with
scale less than equal to -38, For eg Number(38,-39) would result in zeroes being stored in the
table. So could you please explain in which cases are the scale from -38 to -84 useful in number
declaration.
Followup January 14, 2013 – 11am UTC:
ops$tkyte%ORA11GR2> create table t ( x number(38,-39) );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t values (
12345678901234567890123456789012345678000000000000000000000000000000000000000 );
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select * from t;
X
———-
1.2346E+76
it would not just store zeros. You are saying “I want 38 digits of precision – for a really really really large number, because I said there will be 39 zeros before we start seeing digits whose value we care about” |
|
|
|
Why 126?
Followup May 16, 2013 – 8am UTC:
because that is as big as we can go
ops$tkyte%ORA11GR2> insert into t values ( rpad( ‘8’, 126, ‘9’ ) );
1 row created.
ops$tkyte%ORA11GR2> insert into t values ( rpad( ‘8’, 127, ‘9’ ) );
insert into t values ( rpad( ‘8’, 127, ‘9’ ) )
*
ERROR at line 1:
ORA-01426: numeric overflow
but we’ve lost the precision by then:
ops$tkyte%ORA11GR2> create table t ( x number(*,0) );
Table created.
ops$tkyte%ORA11GR2> insert into t values ( rpad( ‘9’, 45, ‘9’ ) );
1 row created.
ops$tkyte%ORA11GR2> select to_char( x ) from t;
TO_CHAR(X)
—————————————-
1.0000000000000000000000000000000000E+45
ops$tkyte%ORA11GR2> set numformat 9999999999999999999999999999999999999999999999999
ops$tkyte%ORA11GR2> select x from t;
X
————————————————–
1000000000000000000000000000000000000000000000 |
|
|
|
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Post a Comment