161

What is the difference between precision and scale in Oracle? In tutorials they usually leave scale empty and set precision to 6 when creating a primary key.

What do precision and scale stand for?

J0e3gan
  • 8,740
  • 10
  • 53
  • 80
user700792
  • 2,199
  • 5
  • 20
  • 15

8 Answers8

223

Precision 4, scale 2: 99.99

Precision 10, scale 0: 9999999999

Precision 8, scale 3: 99999.999

Precision 5, scale -3: 99999000

koljaTM
  • 10,064
  • 2
  • 40
  • 42
  • 18
    can you please explain the behavior of negative scales? – Geek Jun 19 '14 at 13:08
  • 5
    looks like rounds/ignores that many integer values left of decimal – JDPeckham Jun 20 '14 at 03:54
  • 3
    keep in mind that precision is always includes the scale portion. e.g: Precision 4, scale 2 - will fail any number > 99.9999..; try: select cast (99.99999 as NUMBER(4,2)) from dual; //OK; select cast (100.9 as NUMBER(4,2)) from dual; //FAIL; – Jama Djafarov Mar 06 '15 at 17:47
  • @JamaDjafarov 99.99999 fails as can be seen below: ` 21:53:54 CB900@XYZ > select cast (99.99999 as NUMBER(4,2)) from dual; select cast (99.99999 as NUMBER(4,2)) from dual * ERROR at line 1: ORA-01438: value larger than specified precision allowed for this column 21:52:32 CB900@ASCEND1 > select version from v$instance; VERSION --------------------------------------------------- 12.1.0.2.0 ` – Phalgun Jun 25 '17 at 11:58
  • @Phalgun select cast(99.9999 as NUMBER(4,2)) from DUAL; is a bad example because the truncation will round the number up from 99 to 100, which is then too large for a NUMBER(4,2). Try select cast(88.8888 as NUMBER(4,2)) from DUAL; instead to see the response is 88.89. – superbeck Jan 18 '18 at 19:02
  • Why does this happen then: `scala> val bd = BigDecimal("0.01490075") bd: scala.math.BigDecimal = 0.01490075 scala> bd.precision res5: Int = 7` – Jwan622 May 30 '18 at 19:27
  • what if the entire number was negative? does that change anything? or would -99.99 still be (4,2) – dangel Jun 25 '18 at 02:52
60

Precision is the total number of digits, can be between 1 and 38.
Scale is the number of digits after the decimal point, may also be set as negative for rounding.

Example:
NUMBER(7,5): 12.12345
NUMBER(5,0): 12345

More details on the ORACLE website:
https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

Ayman
  • 1,682
  • 15
  • 17
  • And Scale is the number of digits to the right (positive) or left (negative) of the decimal point, not just after the decimal point. – Lalit Kumar B May 28 '15 at 05:11
  • Look at koljaTM's example. Precision is how many significant digits, which may have a value (i.e., not just "0" as place holders). Scale indicates how those fall with respect to the decimal point. Scale can be negative, indicating what precision ABOVE 0 you DON'T care about. NUMBER(1,-4): allows only 10 values: 00000, 10000, 20000...90000 – gordon Nov 07 '19 at 16:12
  • 1
    Precision is NOT JUST the total number of digits. As David Aldridge explained in his comment to manojlds it is the mantissa - how many significant digits you care about. Scale thought of as the exponent is a more accurate, albeit esoteric, explanation. Any negative scale will have no digits after the decimal point, and will have that many 0s as place holders to the left of the decimal point. NUMBER(1,-4) will have 5 digits, but only the first, in the 10,000s place will have a value you care about. – gordon Nov 07 '19 at 16:25
58

Precision is the number of significant digits. Oracle guarantees the portability of numbers with precision ranging from 1 to 38.

Scale is the number of digits to the right (positive) or left (negative) of the decimal point. The scale can range from -84 to 127.

In your case, ID with precision 6 means it won't accept a number with 7 or more significant digits.

Reference:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1832

That page also has some examples that will make you understand precision and scale.

manojlds
  • 290,304
  • 63
  • 469
  • 417
  • 1
    that mean the last bumber will be 1000000? – user700792 Apr 16 '11 at 21:17
  • 9
    +1: I think the key to understanding this is to understand the internal number format -- mantissa and exponent. Precision places a limit on the possible length of the mantissa, and scale places a limit on the possible minimum of exponent. – David Aldridge Jun 11 '13 at 12:03
  • @DavidAldridge I echo you. I think you should consider posting it as an answer regarding the mantissa and exponent. A number is actually stored in a variable length format. – Lalit Kumar B May 28 '15 at 05:10
  • What about precision for CHAR datatype? – Wortig Nov 28 '21 at 17:21
35

Maybe more clear:

Note that precision is the total number of digits, scale included

NUMBER(Precision,Scale)

Precision 8, scale 3 : 87654.321

Precision 5, scale 3 : 54.321

Precision 5, scale 1 : 5432.1

Precision 5, scale 0 : 54321

Precision 5, scale -1: 54320

Precision 5, scale -3: 54000

Community
  • 1
  • 1
Eric Bole-Feysot
  • 13,949
  • 7
  • 47
  • 53
30

Scale is the number of digit after the decimal point (or colon depending your locale)

Precision is the total number of significant digits

scale VS precision

ylerjen
  • 4,109
  • 2
  • 25
  • 43
1

precision: Its the total number of digits before or after the radix point. EX: 123.456 here precision is 6.

Scale: Its the total number of digits after the radix point. EX: 123.456 here Scaleis 3

Bablu Gope
  • 21
  • 3
0
  • Precision: Total length of the data.
  • Scale

    • Scale>0

      • Precision=Integer.Length+Decimal.Length
      • The number of digits after the decimal point.

    • Scale<0

      • Precision=Integer.Length

      If you specify a negative scale, Oracle Database rounds the actual data to the specified number of places to the left of the decimal point. Reference to Oracle

five element god
  • 91
  • 1
  • 4
-6

If value is 9999.988 and Precision 4, scale 2 then it means 9999(it represents precision).99(scale is 2 so .988 is rounded to .99)

If value is 9999.9887 and precision is 4, scale is 2 then it means 9999.99

yogi
  • 15
  • 1
  • 8
    No, precision is the number of significant digits to store. In both your cases the precision stored is 6 and the scale is 2. – David Aldridge Jun 11 '13 at 12:01