Precision and Scale are two basic parts of Oracle number data type, and almost every Oracle SQL developer will encounter it. But recently I was confused on how much exactly Oracle SQL can store when giving precision and scale.
There are topics on SOF (http://stackoverflow.com/questions/5689369/what-is-the-difference-between-precision-and-scale-in-oracle) and also the definitions are given in Oracle document (http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT313).
But one point I couldn’t find anywhere (maybe I didn’t notice in document), is that when exactly Oracle DB will throw error when storing numbers.
I had thought since Precision is number of total digits in a number and Scale is number of digits that right to decimal point, if the Precision is violated, i.e., when total number of digits more than defined Precision, Oracle will throw out error.
But it turns out that both Precision and Scale alone are not used to determine the error. It actually only throw out error when:
Number of Significant Digits (usually digits to the left of decimal point) > Precision – Scale
So if a number type defined as Number(8,2), so we have below scenario:
|123456.12||Valid||Stored as 123456.12|
|123456.123||Valid||Stored as 123456.12|
Similarly, for type Number(8,-2):
|1234567890||Valid||Stored as 1234567800|
|123456.123||Valid||Stored as 123400|
Another interesting thing is that if you define the type as Number only, without giving Precision and Scale, Oracle will automatically take maximum precision which is 38, and use flexible scale based on input. But once you give the Precision, the scale will automatically becomes 0. So Number(38) is same as Number(38,0).