I need help with understanding NUMBER data type, because 2 below statements from Oracle documentation seems to be for me mutually exclusive.
"The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10^-130 to but not including 1.0 x 10^126."
"The precision p can range from 1 to 38. The scale s can range from -84 to 127."
My questions:
Sorry in advance, if the question seems to be obvious for everybody other than me.
How it's possible to handle such big value like 1.0 x 10^126 with precision(number of all digits in a given numeric value) equal 38. As far as I understand the value 1.0 x 10^126 require precision at least 126, because the value need 126 digits to be stored.
Oracle reserves 1 byte for the scale and then stores 2-digits per byte for the precision so if you have a scale of 127 and a precision of 38 then the value stored could be:
99999999999999999999999999999999999999000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
(thirty-eight 9-digits followed by eighty-nine 0-digits)
Any of the 0 digits will not be stored in the database and only the 9 digits would be stored.
Therefore you cannot store a 127-digit number, you can only store the first 38 significant digits of the 127-digit number.
Storing 1e125 is simple, the scale byte would encode the value 125 and then the data would be a single byte encoding the value 1 (Oracle uses a proprietary format so the byte values are not 125 and 1 but you should be able to understand the concept; if you want to see the exact binary values then use the DUMP function).
How it's possible to have precision like 38 and scale bigger than that, ex. 127?
Don't store a number, store a string or store a binary value.
If you are expecting to store it as a numeric data type then you cannot because the limitations of the data-type prevent it.