Default precision of number datatype in Oracle-CodePudding

I have a column in my Oracle table that is of NUMBER datatype.The length of the column's max value is 39 digits long.I thought if the precision is not specified ,by default precision is 38.If so,how can it store a value that is 39 digits long?

CodePudding user response：

Oracle can store/represent numbers up to 39 digits, but it guarantees only 38 digits of precision. This is why it only allows you to specify precision up to 38, and most of the documentation only says "38" and doesn't even mention "39".

As far as I can tell, without being an expert in any way, Oracle doesn't explain WHY this is. But I don't think it's hard to speculate. This must have something to do with power(2, 128) being a number with 39 digits. I believe Oracle can handle numbers up to power(2, 128) - so SOME 39 digit numbers (below that value) can be handled properly, but numbers above that can't, especially if you start doing arithmetic with them. Since Oracle treats numbers as if they were in base 10 (which it can only do in software - in hardware everything is bits), it just states that precision can be "up to 38" (guaranteed), so it doesn't have to explain anything beyond that. But you can, in fact, store 39 digit numbers - just don't expect the 39th digit to be accurate. Sometimes it will be, but sometimes it won't.

EDIT:

Looking again at your question... If your column was defined as NUMBER(38) or NUMBER(38, 0), then the length would indeed be bounded at 38. Your column must have been defined as NUMBER (without specifying precision), which Oracle expands to NUMBER(*). Or, perhaps, the column was defined as NUMBER(*), or INT, INTEGER, or NUMBER(*, 0) - all are mapped to NUMBER(*, 0). The documentation may say that that's "the same as" NUMBER(38) or NUMBER(38, 0) respectively, but in reality it isn't - it's what you saw, and what I explained.

For practical purposes though, if you only want to use documented features of the language, you should treat your column as if precision was limited by 38, even though in reality the numbers may be stored with more significant digits.

CodePudding user response：

I don't know what kind numbers you need storage. But a trick is do / 100, or /1000 before save.. and when you retrieve a data, you do *100, or *1000.

I hope it's useful.