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
|
|
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1416003959639
|
|
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1619552483055
|
|
Number or number(p)
|
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
|
|
|