How can an Oracle NUMBER have a Scale larger than the Precision?

Question

The documentation states: "Precision can range from 1 to 38. Scale can range from -84 to 127".

How can the scale be larger than the precision? Shouldn't the Scale range from -38 to 38?

Answer 1

According to Oracle Documentation:

Scale can be greater than precision, most commonly when ex notation is used (wherein decimal part can be so great). When scale is greater than precision, the precision specifies the maximum number of significant digits to the right of the decimal point. For example, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point.

Here's how I see it :

• When Precision is greater than Scale (e.g NUMBER(8,5)), no problem, this is straightforward. Precision means the number will have a total of 8 digits, 5 of which are in the fractional part (.→), so the integer part (←.) will have 3 digits. This is easy.

• When you see that Precision is smaller than Scale (e.g NUMBER(2, 5)), this means 3 things :

  • The number will not have any integer part, only fractional part. So the 0 in the integer part is not counted in the calculations, you say .12345 not 0.12345. In fact, if you specify just 1 digit in the integer part, it will always return an error.
  • The Scale represents the total number of digits in the fractional part that the number will have. 5 in this case. So it can be .12345 or .00098 but no more than 5 digits in total.
  • The fractional part is divided into 2 parts, significant numbers and zeros. Significant numbers are specified by Precision, and minimum number of zeros equals (Scale - Precision). Example :

  

  here The number will must have a minimum of 3 zeros in the fractional part. followed by 2 significant numbers (could have a zero as well). So 3 zeros + 2 significant numbers = 5 which is the Scale number.

In brief, when you see for example NUMBER(6,9), this tells us that the fractional part will have 9 digits in total, starting by an obligatory 3 zeros and followed by 6 digits.

Here are some examples :

SELECT CAST(.0000123 AS NUMBER(6,9)) FROM dual;   -- prints: 0.0000123; .000|012300
SELECT CAST(.000012345 AS NUMBER(6,9)) FROM dual; -- prints: 0.0000123; .000|012345
SELECT CAST(.123456 AS NUMBER(3,4)) FROM dual;    -- ERROR! must have a 1 zero (4-3=1)
SELECT CAST(.013579 AS NUMBER(3,4)) FROM dual;    -- prints: 0.0136; max 4 digits, .013579 rounded to .0136

Answer 2

The question could be why not ? Try the following SQL.

select cast(0.0001 as number(2,5)) num, 
       to_char(cast(0.0001 as number(2,5))) cnum,
       dump(cast(0.0001 as number(2,5))) dmp
  from dual

What you see is that you can hold small numbers is that sort of structure It might not be required very often, but I'm sure somewhere there is someone who is storing very precise but very small numbers.