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.