1

Create table foo ( ddcup number(23,20) );

While in insert record

insert into foo values (-3260.78510542844)

i'm getting error ORA-01438: value larger than specified precision allowed for this column

enter image description here

How to oracle considered data for number(23,20)

Syan
  • 137
  • 1
  • 5
  • 17
  • 1
    Precision and scale are explained [in the documentaiton](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-75209AF6-476D-4C44-A5DC-5FA70D701B78). – Alex Poole Jul 31 '20 at 14:35
  • Thank @AlexPoole Can you explain in above example. – Syan Jul 31 '20 at 14:52
  • I think the documentation explains it; not sure what you want me to add. [Does this help](https://stackoverflow.com/a/2741455/266304)? – Alex Poole Jul 31 '20 at 14:57
  • As per definition NUMBER(2,2) means 2 digit precision after that 2 digit scale in my case i use Number(23,20) still data is not insert ? – Syan Jul 31 '20 at 15:06
  • Precision is 23, scale is 20 - so 20 places after the decimal point, leaving only 3 places (at most) before the decimal point. This is shown in the examples in the documentation (for 3,2 4,2 and 5,2). If you had `number(2,2)` then you could only have values < 1. Without knowing what numeric values you expect/need to handle it isn't obvious whether the column definition is wrong, or it is correctly preventing bad data being inserted. – Alex Poole Jul 31 '20 at 15:14

1 Answers1

0

If your column is NUMBER(23,20) means that the column will accept any value up to 23 digits and that the precision of this number is up to 20 digits.

But you need to realise that if you have already a precision of up to 20 decimals, because that is the precision you have defined , your integer can only take 3.

SQL> create table t ( c1 number(23,20) ) ;

Table created.

SQL> insert into t values ( 202039.20202020 ) ;
insert into t values ( 202039.20202020 )
                       *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> insert into t values ( 202.20202020 ) ;

1 row created.

SQL> insert into t values ( 2021010101 );
insert into t values ( 2021010101 )
                       *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> insert into t values ( 4000 ) ;
insert into t values ( 4000 )
                       *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL>  insert into t values ( 202.20202020330992223 ) ;

1 row created.

SQL>
Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43