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

29,914

Solution 1

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.

Solution 2

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

Solution 3

Thanks to everyone for the answers. It looks like the precision is the number of significant digits.

 select cast(0.000123 as number(2,5)) from dual

results in:

.00012

Where

 select cast(0.00123 as number(2,5)) from dual

and

 select cast(0.000999 as number(2,5)) from dual

both result in:

ORA-01438: value larger than specified precision allowed for this column

the 2nd one due to rounding.

Solution 4

The case where Scale is larger than Precision could be summarized this way:

Number of digits on the right of decimal point = Scale

Minimum number of zeroes right of decimal = Scale - Precision

--this will work 
select cast(0.123456 as number(5,5)) from dual;

returns 0.12346

-- but this
select cast(0.123456 as number(2,5)) from dual;
--will return "ORA-1438 value too large".  
--It will not return err with at least 5-2 = 3 zeroes:
select cast(0.000123456 as number(2,5)) from dual;

returns 0.00012

-- and of course this will work too
select cast(0.0000123456 as number(2,5)) from dual;

returning 0.00001

Solution 5

According to Oracle Documentation:

Scale can be greater than precision, most commonly when e notation is used. 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.

It is good practice to specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, then Oracle returns an error. If a value exceeds the scale, then Oracle rounds it.

Share:
29,914
aiGuru
Author by

aiGuru

I’m a truly lucky person to make a living doing something I am passionate about: creating software. I started programming when I was 10 after seeing SHRDLU on television... watching a robotic arm understand and correctly react to the command “Pick up a big red block” is a moment forever burned in my memory. Not long after that I played Intellivision at my cousin Matt’s house which occupied countless hours of my youth. I was never satisfied with simply playing the games, I wanted to know what was going on behind the scenes. At the same time my parents gave me my first computer: a TI99/4A, and my passion for creating software took root. The first program I wrote was not "Hello World", it was a chatter bot. I coded many games on that machine, including a Wizardry clone, which I would trade (via audio cassettes) with my like minded schoolmates. Since then I've worked for a Fortune 500 company (AutoZone), an iconic American company (Yankee Candle), an indie game company making games for the blind (All inPlay), and I even took a stab at my company creating artificial intelligence tools and educational software. I've been coding for almost 30 years - most of it professionally - and I love that I "work" in a profession where every day I learn something new and have the potential to change lives, heck, there are a couple marriages and even kids that resulted from some of my work at All inPlay.

Updated on July 09, 2022

Comments

  • aiGuru
    aiGuru over 1 year

    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?