Ошибка ora 01401

Одобрено: Fortect

Повысьте производительность вашего компьютера с помощью этой простой загрузки. г.

Сегодняшнее руководство предназначено для того, чтобы помочь вам, когда вы получаете ошибку Oracle ora-01401 вставленное значение слишком велико для столбца сообщение об ошибке продажи.

ДобавленоПричина: Приз почти наверняка превышает максимальный размер столбца la. Действие: Введите значение меньше ширины улыбки или используйте эту опцию ИЗМЕНИТЬ с ИЗМЕНЕНИЕМ ТАБЛИЦЫ, чтобы увеличить ширину столбца.

<цитата блока>

Ошибка SQL: ORA -0 был заменен на ORA-12899, который является более явным и имеет варианты SCHEMA и TABLE, чем COLUMN. вызвало ошибку.

Существует основной ORA-0: значение ошибки слишком много, вставлено как для столбца", как узнать, какой столбец вызывает ошибку.
Спасибо. Отличная работа, чем вы обязательно займетесь.

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>

As per oracle documentation

NCHAR and NVARCHAR2 Datatypes

NCHAR and NVARCHAR2 are Unicode datatypes that store Unicode character data. They are also called as Native data types.

The NVARCHAR2 datatype stores variable length character strings.

When you create a table with an NVARCHAR2 column, the maximum size specified is always in character length semantics. Character length semantics is the default and only length semantics for NVARCHAR2.

For example, if national character set is UTF8, then the following statement defines the maximum byte length of 90 bytes:

CREATE TABLE tab1 (col1 NCHAR(30));

The maximum length of an NVARCHAR2 column is 4000 bytes. It can hold up to 4000 characters. The actual data is subject to the maximum byte limit of 4000. The two size constraints must be satisfied simultaneously at run time.

It all depends on your character set and length semantics.

Reason for your problem is

One other important thing to remember is that the upper bound of the
number of bytes stored in a VARCHAR2 is 4,000. However, even if you
specify VARCHAR2(4000 CHAR), you may not be able to fit 4,000
characters into that field. In fact, you may be able to fit as few as
1,000 characters in that field if all of the characters take 4 bytes
to be represented in your chosen character set!

This holds good for Native datatype also.

Solution:

Use CHAR instead of BYTES as the length semantics for VARCHAR2 or CHAR.

The reason is that a 20-character string in a single-byte character
set is 20 bytes long and will absolutely fit in a VARCHAR2(20).
However a 20-character field could be as long as 80 bytes in a
multibyte character set, and 20 Unicode characters may well not fit in
20 bytes.