Oracle error 12899

Так случилось, что продукт, который мы разрабатываем работает с несколькими реляционными базами данных. Сейчас это 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.

[{«Product»:{«code»:»SS6QYM»,»label»:»Sterling Selling and Fulfillment Suite»},»Business Unit»:{«code»:»BU055″,»label»:»Cognitive Applications»},»Component»:»Not Applicable»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»All»,»Edition»:»»,»Line of Business»:{«code»:»LOB59″,»label»:»Sustainability Software»}}]