Search code examples
oracleplsqloracle-sqldeveloper

how to Consider Datatype in Oracle NUMBER(23,20)

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)

Solution

  • 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>