varchar2 и Unicode для тех, кто ничего не понимает в базах данных Oracle или ORA-12899: value too large for column

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