SQL Error: ORA-01401: inserted value too large for

问题:

I am getting issue while inserting a new record to the table in Oracle database. The error description says-

SQL Error: ORA-01401: inserted value too large for column

How would I come to know that which column is having large value as I am having 60 columns in the table.

回答1:

SQL Error: ORA-01401: inserted value too large for column

You are trying to insert value larger than the specified size for the column.

How would I come to know that which column is having large value as I am having 60 columns in the table.

The error will certainly have the table and column name with the actual size being inserted and the maximum size allowed.

For example,

SQL> CREATE TABLE t(A VARCHAR2(2));

Table created.

SQL>
SQL> INSERT INTO t VALUES ('123');
INSERT INTO t VALUES ('123')
                      *
ERROR at line 1:
ORA-12899: value too large for column "LALIT"."T"."A" (actual: 3, maximum: 2)


SQL>

In the above example, the error clearly states "column "LALIT"."T"."A" (actual: 3, maximum: 2)" where LALIT is the SCHEMA, T is the TABLE and A is the COLUMN. The size specified for column A while table creation was 2, however, the actual insert had 3.

UPDATE Regarding confusion between ORA-01401 and ORA-12899.

From Oracle 10g and higher, the ORA-01401 was modified to ORA-12899 which is more explicit and has the details about the SCHEMA, TABLE and the COLUMN which caused the error.

Additional information Just in case if anyone is interested:

There is a counterpart of ORA-01401, i.e. ORA-01438 which is applicable in case of NUMBER. This seems to be unchanged.

For example,

SQL> CREATE TABLE t(A number(2));

Table created.

SQL>
SQL> INSERT INTO t VALUES (123);
INSERT INTO t VALUES (123)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL>