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

Cause:

I was trying to insert rows with values greater than what variables capacity that i assigned when creating the table

SQL> insert into sal (sid, sname, sstate,samount,sdate)
  2  select million_sequence.nextval, dbms_random.string('a','20'), dbms_random.string('berlin','20'),
floor(dbms_random.value(1,10)),to_date('01-JAN-2020','DD-MON-YYYY')+trunc(dbms_random.value(1,1000))
from dual
connect by level <= 1000000;  3    4    5
select million_sequence.nextval, dbms_random.string('a','20'), dbms_random.string('berlin','20'),
       *
ERROR at line 2:
ORA-01438: value larger than specified precision allowed for this column

Workaround:

This is the ddl which i used to create table where sid capacity or the data that can stored in column sid is upto 5 units of number. But the rows are exceeding the precision value declared in the datatype

CREATE TABLE sal  
(  
   sid   NUMBER(5)    NOT NULL,  
   sname VARCHAR2(30),  
   sstate   VARCHAR2(20) NOT NULL,  
   samount  NUMBER(10),  
   sdate    DATE         NOT NULL  
)  

Modify the column value to higher value

SQL> alter table sal modify sid number(38);

Table altered.

After increasing the datatype value, the insert happened successfully

SQL> insert into sal (sid, sname, sstate,samount,sdate)
  2  select million_sequence.nextval, dbms_random.string('a','20'), dbms_random.string('berlin','20'),
floor(dbms_random.value(1,10)),to_date('01-JAN-2020','DD-MON-YYYY')+trunc(dbms_random.value(1,1000))
from dual
connect by level <= 1000000;
  2    3    4    5

1000000 rows created.

SQL> SQL> commit;

Commit complete.

Leave a Reply