Hi,

I got ” ORA-01438: value larger than specified precision allowed for this column” error in Oracle database.

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

Details of error are as follows.

SQL> create table test_table (id number(3,3));

Table created.

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


SQL>

The ORA-01438 error is related with the value larger than column’s width is inserted in the column.

Number datatype columns are used in precision and scale (NUMBER(p,s)).  If you define a number column as “NUMBER(4,2)”, you need to use 4 maximum width of the data and 2 for decimal part like (123.45)

ORA-01438

To solve The ORA-01438 error, use the correct value as follows.

SQL> insert into test_table values(0.3);

1 row created.


SQL> insert into test_table values(0.2);

1 row created.

SQL> insert into test_table values(0.1);

1 row created.

SQL>

Second example is as follows.

SQL> CREATE TABLE test
2 ( id number(6) not null,
3 name varchar2(40) not null
4 );

Table created.

SQL> insert into test values(1453634,'Mehmet Salih');
insert into test values(1453634,'Mehmet Salih')
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

To solve this error use only 6 digits for the first column as follows.

SQL> insert into test values(145363,'Mehmet Salih');

1 row created.

SQL>

Or another example is as follows.

SQL> create table test (id number(6,3));

Table created.

SQL> insert into test values(12.3456);

1 row created.

SQL> insert into test values(123.4567);

1 row created.

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


SQL> insert into test values(123.45678);

1 row created.

SQL> insert into test values(123.456789);

1 row created.

SQL> insert into test values(123.4567890);

1 row created.

SQL> insert into test values(123.4567890123);

1 row created.

SQL>

Do you want to learn Oracle SQL, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course