How can Oracle NUMBER scale more accurately?

Question

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

How can scale be greater than accuracy? Should the scale range from -38 to 38?

Answer 1

The question may 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, it’s such a structure. It may not be required very often, but I’m sure that somewhere there is someone who stores very accurate, but very small numbers.

Answer 2

. , - .

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

:

.00012

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

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

:

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

- .

Answer 3

Oracle:

, , . , . , , NUMBER (4,5), .

. . , Oracle . , Oracle .

Answer 4

, , :

=

= -

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

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;

0.00012

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

0.00001

Answer 5

Oracle:

, , ex notation ( ). , . , , NUMBER(4,5), .

:

• Precision (, NUMBER(8,5)), , . Precision , 8 , 5 (. →), (←.) 3 . .

• , Precision , Scale (, NUMBER(2, 5)), 3 :

  • , . , 0 , .12345 0.12345. , , .
  • Scale , . 5 . , .12345 .00098, 5 .
  • 2 , . Precision, (Scale - Precision). :

  

  3 . 2 ( ). , 3 + 2 = 5, Scale.

, , , NUMBER(6,9), , 9 , 3 6 .

:

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 6

, , - .
maximum precision of 126 binary digits, which is roughly equivalent to 38 decimal digits

NUMBER (, ), - , - . , . (, , NUMBER (*, 10)) - , , 10

, scale , .
, , , - 0,00000000123456, 100%.