Is "NUMBER" and "NUMBER(*,0)" the same in Oracle?
In Oracle documentation it is mentioned that
NUMBER (precision, scale)
If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.
But NUMBER (without precision and scale) is also accepting floating point numbers (34.30) but according to documentation if scale is not specified it should be zero scale by default so it should allow only integers, am I wrong?.
And in another questions it is mentioned that
default precision is 38, default scale is zero
So NUMBER and NUMBER(*,0) should be equal but they are not.
Where am I wrong?
Answer
I think the sentence in the documentation
If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.
is a bit confusing. The scale is zero if a precision is specified and a scale is not specified. So, for example, NUMBER(19) is equivalent to NUMBER(19,0). NUMBER, by itself, will have 38 digits of precision but no defined scale. So a column defined as a NUMBER can accept values of any scale, as long as their precision is 38 digits or less (basically, 38 numerical digits with a decimal point in any place).
You can also specify a scale without a precision: NUMBER(*, <scale>), but that just creates the column with 38 digits of precision so I'm not sure it's particularly useful.
The table How Scale Factors Affect Numeric Data Storage on this page might be helpful.