Ошибка ora 12899

I just wanted to share here as the top result from Google…

I was trying to add virtual column like this

alter table t1 add (
    export_code varchar2(3 char) as (case when country is null then code else substr(code, 3) end) virtual
);

and oracle complained:

ORA-12899: value too large for column "EXPORT_CODE" (actual: 12, maximum: 480)

The problem is, that my code column is varchar2(120) so substr is up to 120 characters.

One can find, when creating a temporary table:

create table t2 as
    select case when country is null then code else substr(code, 3) end as export_code from t1;

and desc returns

Name        Null? Type          
----------- ----- ------------- 
EXPORT_CODE       VARCHAR2(120) 

once I realized, the solution is to use export_code varchar2(120 char) or do the cast:

alter table t1 add (
  export_code varchar2(3 char) as ( cast (case when country is null then code else substr(code, 3) end as varchar2(3 char) ) ) virtual
);

You’re seeing the difference between character and byte length semantics:

You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte, although the actual string stored is permitted to be a zero-length string (»). You can use the CHAR qualifier, for example VARCHAR2(10 CHAR), to give the maximum length in characters instead of bytes. A character is technically a code point of the database character set. You can use the BYTE qualifier, for example VARCHAR2(10 BYTE), to explicitly give the maximum length in bytes. If no explicit qualifier is included in a column or attribute definition when a database object with this column or attribute is created, then the length semantics are determined by the value of the NLS_LENGTH_SEMANTICS parameter of the session creating the object.

If your session is using byte semantics then the column in your table will default to that:

select value from nls_session_parameters where parameter = 'NLS_LENGTH_SEMANTICS';

VALUE                                  
----------------------------------------
BYTE                                    

create table t42(text varchar2(5));

Table T42 created.

select char_used from user_tab_columns where table_name = 'T42' and column_name = 'TEXT';

C
-
B

That is the same as explicitly doing:

create table t42(text varchar2(5 byte));

If your source data is five characters but includes any multibyte characters then the number of bytes will exceed five:

insert into t42 (text) values ('Hello');

1 row inserted.

insert into t42 (text) values ('Señor');

SQL Error: ORA-12899: value too large for column "SCHEMA"."T42"."TEXT" (actual: 6, maximum: 5)

Which is what you’re seeing. When you insert the values from your other table you are filter on the length of the values, but length() counts characters rather than bytes. There is a lengthb() function that does count bytes. If you check the byte length of the 30-character value you’re selecting you’ll see it is in fact 31 bytes, so one of those characters is multibyte.

with t42 (text) as (
  select 'Hello' from dual
  union all select 'Señor' from dual
  union all select 'Testing  - HLC/TC Design Corre' from dual
)
select text, length(text) as chars, lengthb(text) as bytes, dump(text, 16) as hex
from t42;

TEXT                            CHARS BYTES HEX                                                                                                      
------------------------------- ----- ----- ----------------------------------------------------------------------------------------------------------
Hello                               5     5 Typ=1 Len=5: 48,65,6c,6c,6f                                                                               
Señor                               5     6 Typ=1 Len=6: 53,65,c3,b1,6f,72                                                                            
Testing  - HLC/TC Design Corre     30    31 Typ=1 Len=31: 54,65,73,74,69,6e,67,c2,a0,20,2d,20,48,4c,43,2f,54,43,20,44,65,73,69,67,6e,20,43,6f,72,72,65

From the dump() values you can see that after Testing (54,65,73,74,69,6e,67) and before the space and dash (20,2d) you have c2,a0, which is the UTF-8 multibyte non-breaking space character. (You often see that, along with curly quotes and other non-ASCII-range characters, in text that has been copied from,. say, a Word document).

You can either change your insert to filter on LENGTHB(column1)=30 (which will exclude the row you currently find), or change your column definition to 30 characters instead of 30 bytes:

drop table t42;

Table T42 dropped.

create table t42(text varchar2(5 char));

Table T42 created.

select char_used from user_tab_columns where table_name = 'T42' and column_name = 'TEXT';

C
-
C

insert into t42 (text) values ('Hello');

1 row inserted.

insert into t42 (text) values ('Señor');

1 row inserted.

Or replace any unexpected multibyte characters with single-byte equivalents, if that’s possible and makes sense for your data; in this case a normal space might work, but with any substitution you are destroying information that might actually be important.

Время на прочтение
3 мин

Количество просмотров 11K

Так случилось, что продукт, который мы разрабатываем работает с несколькими реляционными базами данных. Сейчас это MS SQL, Postgres и Oracle. Были запуски под много чем от MySQL до покойного, наверное, Firebird и экзотических Sybase с DB2, но сказ не об этом.

Если с MS SQL и Postgres все более мене понятное-привычное, то с Oracle каждый раз нас ждут какие-то сюрпризы. Проницательный читатель сразу заметит, что «руки у нас кривые» и мы «попросту не умеем его готовить», но если, уважаемому читателю захочется узнать чем varchar (а точнее varchar2) в Богоподобном Oracle отличается от его собратьев, то прошу под кат.

Как все современные системы, мы храним данные в Unicode формате (в данный момент это UTF-8). Почему это может быть важно для реляционных баз данных?

Ну, например, если у вас в базе данных mix unicode и non-unicode типов данных, то некоторые драйвера в такое не могут. Например, JTDS — JDBC драйвер для MS SQL сервера может работать либо в Unicode режиме, либо в Ansi. Соответственно, если Вы решите «сэкономить» и создать не unicode колонку (varchar/char), то получите преобразование unicode->ansi на уровне вставки данных в таблицу и, скорее всего, достигните обратного эффекта (как минимум замедления на вставке данных, а то и на поиске).

Итак, история. Наш сервер приложений проверяет максимальную допустимую длину полей до их вставки (здесь нужно оговориться, что проверка выполняется не по данным БД, а по нашим внутренним метаданным), но несмотря на это иногда под Oracle мы «ловим» ошибку вида ORA-12899: value too large for column.

Что за напасть? Причем, скрипты генерируются примерно одним и тем же способом под все базы данных, но проблема возникает только иногда и только под Oracle.

Не буду томить. Оказалось, что мы невнимательно прочитали спецификацию типа varchar2 в котором хранятся данные  

Давайте изменим размер колонки, например, на следующий

alter table address modify street varchar2(150);

Как Вы думаете 150 — это длина в символах (как в других базах в общем-то)? Подсказка — нет Скорее всего в байтах.

А в символах это

alter table address modify street varchar2(150 char);

Т.е. не указывая спецификацию char—byte мы оказываемся в серой зоне настроек базы данных по умолчанию. Причем во всех базах до которых мы смогли дотянуться (включая продакшн и не только наши)  настройка по умолчанию — это байты.

А теперь давайте вспомним, что в UTF-8, например, один символ может занимать от одного до 4 байт (обычно 1 байт ANSI, 2 русские символы и некоторые которым больше повезло и до 4 для иероглифов).

И что это за дикая настройка по умолчанию для Unicode баз!? Но ведь, именно она, зараза такая, включена «из коробки». Ну т.е. да, я все понимаю: legacy, обратная совместимость для тех времен, когда Unicode’а еще и «в проекте не было», гордость за то, что backup 86 года можно восстановить последней редакцией imp — вот это вот все.

А почему ошибка возникала только иногда и только для некоторых колонок? Так как тот tool, которым мы генерируем базу изначально был настолько умным, что сразу в create table для всех колонок явно прописывал суффикс char

Выводы:

Неплохо бы иногда проверять, не прокрался ли враг или, если Вы достаточно смелый, изменить эту настройку по умолчанию.

Скрипт для определения значения по умолчанию

SELECT value FROM NLSDATABASEPARAMETERS WHERE parameter='NLSLENGTHSEMANTICS';

Скрипт, который позволяет проверить, что у вас в базе «все ОК»:

SELECT TABLE_NAME, COLUMN_NAME, DATA_LENGTH, CHAR_USED 
FROM USER_TAB_COLUMNS 
WHERE DATA_TYPE = 'VARCHAR2' AND CHAR_USED = 'B'
ORDER BY TABLE_NAME, COLUMN_NAME

P.S. Сразу оговорюсь, это нормально, если там где Вы это ожидаете размерность в байтах (например, там где 100% ansi символы), но вот для Unciode текста … Ушел плакать дальше на эту тему …

P.P.S. Regexp которым можно попробовать найти скрипты «серой зоны» varchar2(s*d+s*)

P.P.P.S. Поиск ответа на этот вопрос с помощью StackOverflow

P.P.P.P.S. А вот, что думает Oracle по поводу изменения значения параметра NLSLENGTHSEMANTICS на что-то более разумное «Oracle strongly recommends that you do NOT set the NLSLENGTHSEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in runtime errors, including buffer overflows.» https://docs.oracle.com/cd/E2469301/server.11203/e24448/initparams149.htm

ORA-12899 means that the value of being inserted or updated data is larger than column’s allowable length that is defined in the table.

What will you do for larger values? Make the column wider? Yes, that’s correct answer. The formal solution to ORA-12899 is to make the column wider to accommodate potential larger values..

In this post, I’ll talk about a special case, it’s about NLS_LENGTH_SEMANTICS. Let’s see how I found ORA-12899 when I migrated some tables from one database (UTF8) to another (AL32UTF8).

SQL> INSERT INTO "ERPAPP"."FIN_NORTH_HIST"@ORA12C SELECT * FROM "ERPAPP"."FIN_NORTH_HIST";
INSERT INTO "ERPAPP"."FIN_NORTH_HIST"@ORA12C SELECT * FROM "ERPAPP"."FIN_NORTH_HIST"
*
ERROR at line 1:
ORA-12899: value too large for column "ERPAPP"."FIN_NORTH_HIST"."MAT"
(actual: 3, maximum: 1)
ORA-02063: preceding line from ORA12C

Check Column Definition

Let’s see the definition of said column in the target database.

SQL> column data_type format a10;
SQL> select data_type, char_length, char_used from dba_tab_cols where owner = 'ERPAPP' and table_name = 'FIN_NORTH_HIST' and column_name = 'MAT';

In which, value B in column CHAR_USED means BYTE in DBA_TAB_COLS. That is to say, the semantics of this column is BYTE. By the way, I think you’d better to know the difference between BYTE and CHAR for NLS_LENGTH_SEMANTICS before reading further.

Solution to ORA-12899

Since BYTE semantics within same length is not safe for data migration, so changed the semantics of this column from BYTE to CHAR for databases with multi-byte language. For each character, UTF-8 takes 1 to 4 bytes to store.

SQL> alter table "ERPAPP"."FIN_NORTH_HIST" modify(MAT char(1 char));

Or you can just increase the column width and stay with BYTE.

SQL> alter table "ERPAPP"."FIN_NORTH_HIST" modify(MAT char(4 byte));

Either way should work.

Check Column Definition

Let’s Check the definition of this column again.

SQL> select data_type, char_length, char_used from dba_tab_cols where owner = 'ERPAPP' and table_name = 'FIN_NORTH_HIST' and column_name = 'MAT';

OK! Now the semantics of this column is CHAR, let’s do it again.

SQL> INSERT INTO "ERPAPP"."FIN_NORTH_HIST"@ORA12C SELECT * FROM "ERPAPP"."FIN_NORTH_HIST";

Good! No ORA-12899.

There’s still a question in my mind. Why would this happen? Since AL32UTF8 is in fact a superset of UTF8, it should have no problem on column width during data migration. I suspect that the column was encoded by non-UTF8-based character set in the first place.

Вы видите разницу между символом и семантикой длины байта:

Вы должны указать максимальную длину для столбца VARCHAR2. Этот максимум должен быть не менее 1 байт, хотя фактической сохраненной строке разрешено быть строкой нулевой длины (”). Вы можете использовать квалификатор CHAR, например VARCHAR2 (10 CHAR), чтобы указать максимальную длину в символах вместо байтов. Символ является технически кодовой точкой набора символов базы данных. Вы можете использовать квалификатор BYTE, например VARCHAR2 (10 BYTE), чтобы явно указать максимальную длину в байтах. Если явный классификатор не включен в определение столбца или атрибута при создании объекта базы данных с этим столбцом или атрибутом, то семантика длины определяется значением параметра NLS_LENGTH_SEMANTICS сеанса, создающего объект.

Если ваш сеанс использует семантику байтов, столбец в вашей таблице будет по умолчанию:

select value from nls_session_parameters where parameter = 'NLS_LENGTH_SEMANTICS';

VALUE                                  
----------------------------------------
BYTE                                    

create table t42(text varchar2(5));

Table T42 created.

select char_used from user_tab_columns where table_name = 'T42' and column_name = 'TEXT';

C
-
B

То же самое, что и явное:

create table t42(text varchar2(5 byte));

Если ваши исходные данные составляют пять символов, но содержат любые многобайтовые символы, количество байтов будет превышать пять:

insert into t42 (text) values ('Hello');

1 row inserted.

insert into t42 (text) values ('Señor');

SQL Error: ORA-12899: value too large for column "SCHEMA"."T42"."TEXT" (actual: 6, maximum: 5)

Это то, что вы видите. Когда вы вставляете значения из другой таблицы, вы фильтруете длину значений, но length() подсчитывает символы, а не байты. Существует функция lengthb(), которая подсчитывает байты. Если вы проверите длину байта 30-значного значения, которое вы выбрали, вы увидите, что оно фактически равно 31 байту, поэтому один из этих символов является многобайтным.

with t42 (text) as (
  select 'Hello' from dual
  union all select 'Señor' from dual
  union all select 'Testing  - HLC/TC Design Corre' from dual
)
select text, length(text) as chars, lengthb(text) as bytes, dump(text, 16) as hex
from t42;

TEXT                            CHARS BYTES HEX                                                                                                      
------------------------------- ----- ----- ----------------------------------------------------------------------------------------------------------
Hello                               5     5 Typ=1 Len=5: 48,65,6c,6c,6f                                                                               
Señor                               5     6 Typ=1 Len=6: 53,65,c3,b1,6f,72                                                                            
Testing  - HLC/TC Design Corre     30    31 Typ=1 Len=31: 54,65,73,74,69,6e,67,c2,a0,20,2d,20,48,4c,43,2f,54,43,20,44,65,73,69,67,6e,20,43,6f,72,72,65

Из значений dump() вы можете видеть, что после Testing (54,65,73,74,69,6e,67) и перед пробелом и тире (20,2d) у вас есть c2,a0, который многобайтовый неразрывный пробел UTF-8. (Вы часто видите, что вместе с фигурными кавычками и другими символами диапазона, отличными от ASCII, в тексте, который был скопирован, например, из документа Word).

Вы можете либо изменить свою вставку на фильтр на LENGTHB(column1)=30 (который исключает найденную вами строку), либо изменить определение столбца на 30 символов вместо 30 байтов:

drop table t42;

Table T42 dropped.

create table t42(text varchar2(5 char));

Table T42 created.

select char_used from user_tab_columns where table_name = 'T42' and column_name = 'TEXT';

C
-
C

insert into t42 (text) values ('Hello');

1 row inserted.

insert into t42 (text) values ('Señor');

1 row inserted.

Или заменить любые неожиданные многобайтовые символы однобайтными эквивалентами, если это возможно и имеет смысл для ваших данных; в этом случае нормальное пространство может работать, но с любой заменой вы уничтожаете информацию, которая может быть действительно важной.

ОР-12899

Часто, поскольку наши компании растут и развиваются в ответ на расширение в виде клиентской базы, персонала, прибыли или рынков, данные, связанные с этим ростом, также будут меняться. Системы данных, такие как У Oracle есть врожденная способность оставаться достаточно гибкой в ​​отношении работая с этим изменением информации. Тем не менее, даже самые универсальные системы баз данных требуют обслуживания и доработки в лицом повышенного трафика данных. Эта работа имеет важное значение для с учетом любых ограничений на память или необходимых переопределений параметры. Ошибка ORA-12899 является представителем экземпляра в который либо всплеск данных, либо ошибка пользователя заставляет Oracle в течение запрошенного действия.

ПРОБЛЕМА

ORA-12899 – это ошибка Oracle, которая возникает, когда введенное значение в строку столбца слишком велико. Это означает, что пользователь попытался обновить или вставить столбец со значением который слишком широк для столбца назначения. Название конкретного столбец и фактическая ширина значения, а также максимальная ширина, разрешенная для столбца, будет связана с этим. Как уже упоминалось, значение может быть задано в виде символов. в что ширина будет указана в символах, это будет означать, что семантика длины символа работает для столбца. В противном случае ширина будет сообщена в байтах. По сути, эта ошибка возникает из пытаясь проталкивать значение или набор значений, превышающих указанная максимальная ширина столбца. Итак, как пользователь исправляет этот тип ошибки?

РЕШЕНИЕ

Для начала откройте служебную программу OERR. Пользователь потребует полного ORA-12899 для получения правильной обратной связи по ошибке. Эта будет предоставлять дополнительную информацию об ошибке и расследование. Как правило, ошибка может исходить от одного из трех источники. Первым источником являются инструкции SQL, которые были генерироваться. Проверка типов данных столбцов источника и получателя на выясните, совместимы ли они с текущими форматами. второстепенный источник. Наконец, пользователь может посмотреть столбец назначения width – где значение присваивается – чтобы убедиться, что он большой достаточно для размещения максимального значения, которое пользователь ожидает назначения. Обратимся теперь к примеру, который исправляет ORA-12899. Предположим, что пользователь создал следующую таблицу:

CREATE TABLE Clients(
ClientID varchar2(9) PRIMARY KEY,
Client_Contact varchar2(40) NOT NULL,
Address varchar(20) NOT NULL,
Zip varchar2(5) NOT NULL,
Client_Number varchar2(11) NOT NULL)

Затем пользователь пытается выдать оператор INSERT VALUES, который выглядит что-то вроде этого:

INSERT INTO Clients VALUES(
727546345,
‘Roger Holcomb’,
‘—Benjamin Road Syracuse’,
‘-----‘, 05307623754)

Пользователь может попытаться запустить инструкцию здесь, но получит следующее сообщение об ошибке:

Ошибка, начиная с строки 7 в команде: INSERT INTO Клиенты ЦЕННОСТИ (727546345, “Рики Галорей”, 18 Бенджамин Роуд Сиракузы, ‘13208, 05307623754) Отчет об ошибке: Ошибка SQL: ORA-12899: значение тоже большой для столбца “ОРГАНИЗАЦИИ”. “РЫНОК”. “АДРЕС” (актуально: 25, максимум: 20) 12899. 00000 – значение слишком велико для столбца% s (фактическое: % s, максимум:% s) “

Этот оператор ошибки указывает, что переменная ‘Address не может содержать более двадцати символов, так как это превысит ширину столбца. Когда мы оглянемся на значение адреса (18 Бенджамин Road Syracuse), мы видим, что общее количество символов (25) превышает максимальное допустимое значение ширины столбца. к исправьте это, пользователь может изменить VARCHAR2 для адреса на количество, которое может соответствовать типичной длине адреса, компания будет вводить.

Вышеуказанный исходный URL-адрес ответа