Советы, рекомендации, опыт

Олег Летаев,
Директор Центра компетенции Leaves,
Санкт-Петербург

Установка NLS-параметров на клиентских местах пользователей Oracle

Поддержка национальных языков появилась в СУБД Oracle лет 10 тому назад, но и по сей день встречаются случаи неполного понимания работы этого механизма даже у достаточно опытных АБД. Один из таких случаев, недавно имевший место в моей практике, и дал идею написания этой статьи. В ней помимо главной темы “Установка NLS-параметров на клиентских местах пользователей Oracle” речь пойдет и об установках уровня экземпляра и БД, как-то: язык сообщений об ошибках в alert-файле, кодовые страницы БД, представления NLS_Instance_Parameters, NLS_Database_Parameters и т.д.

Поддержка национальных языков предусматривает не только перевод сообщений СУБД и инструментальных средств, но и поддержку различных кодовых страниц, включая конвертацию символьных данных между ними, форматов даты и времени, форматов чисел, денежных единиц, лингвистических порядков сортировки и т.д. Начиная с версии 8.0, каждая инсталляция СУБД Oracle может поддерживать две различные кодовые страницы при хранении символьных данных: основную и национальную. В столбцах типов CHAR, VARCHAR2 и CLOB символьные данные хранятся в кодировке, которая была установлена фразой CHARACTER SET оператора CREATE DATABASE. В столбцах типов NCHAR, NVARCHAR2 и NCLOB символьные данные хранятся в кодировке, которая была установлена фразой NATIONAL CHARACTER SET оператора CREATE DATABASE. Наличие двух кодовых страниц хранения позволяет сохранять в БД данные, по крайней мере, на двух языках, не считая английского, одновременно.

Переменная NLS_LANG, окружение и реестр

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

Переменная NLS_LANG может быть установлена либо в реестре (для Windows-платформ), либо в файле определения параметров среды окружения (для UNIX- и других платформ), либо непосредственно в командной строке перед запуском приложения из командной строки.

Переменная NLS_LANG – это параметр клиентской сессии. Даже если установить ее на сервере, она будет относиться только к клиентским сессиям, открытым непосредственно с сервера. Эта переменная не оказывает никакого влияния ни на фоновые процессы экземпляра Oracle, ни на серверные процессы.

Переменная NLS_LANG имеет следующий формат:

<Язык>_<Территория>.<Кодовая страница>

Малоизвестный факт: не обязательно задавать все три компонента переменной NLS_LANG. Если опустить <Язык>, то по умолчанию будет принят язык American. Тогда формат переменной должен быть следующим:

_<Территория>.<Кодовая страница>

Если опустить <Территория>, то по умолчанию будет принята территория, соответствующая языку. Тогда формат переменной должен быть следующим:

<Язык>_.<Кодовая страница>

Если опустить и <Язык>, и <Территория>, то по умолчанию будет принято American_America. Тогда формат переменной должен быть следующим:

.<Кодовая страница>

<Язык>, и <Территория>, заданные в переменной NLS_LANG или в параметрах NLS_LANGUAGE и NLS_TERRITORY, не имеют отношения к хранению данных в БД. То есть, задание, например, значения JAPANESE_JAPAN.CL8MSWIN1251 не позволяет помещать японские символы в БД, а только получать системные сообщения на японском языке на клиентском компьютере. Если переменная NLS_LANG содержит ошибку, попытка открыть сессию (явное или неявное использование команды connect) закончится ошибкой ORA-12705.

Обычно больше всего ошибок допускается при выборе кодовой страницы. Переменная NLS_LANG должна называть кодовую страницу, которая реально используется на клиентском компьютере. Эта установка служит для того, чтобы Oracle “знал”, какая кодовая страница используется на клиентском компьютере, и правильно конвертировал символьные данные при пересылке между сервером (хранением) и клиентом (представлением), правильно использовал файлы служебных сообщений своих клиентских компонент при получении ими номеров сообщений от сервера.

Кодовая страница, заданная переменной NLS_LANG, может совпадать, а может и не совпадать с кодовой страницей БД. Более того, из чисто логических соображений связь должна быть обратной: кодовая страница БД должна совпадать с кодовой страницей, которую используют большинство клиентских компьютеров. Это минимизирует количество конвертаций данных, экономя тем самым время (производительность), минимизирует ошибки при конвертации из-за отсутствия в одной кодовой странице отдельных символов из другой кодовой страницы. Распространенная ошибка – ставить кодовую страницу БД, расположенной на UNIX-сервере, в значение CL8KOI8R, исходя из того, что КОИ-8 – это “родная” кодировка для UNIX-платформ, и совершенно не учитывая того, что все клиентские приложения этой БД работают под Windows. А следствием этого становится, например, некорректная конвертация столь любимого пользователями символа №.

В общем случае, кодовая страница БД должна быть надмножеством кодовой страницы клиента или, по крайней мере, содержать все используемые клиентскими приложениями и пользователями символы. Конвертация данных из одной кодовой страницы в другую обычно выполняется на стороне клиента. Исключением является случай, когда кодовая страница БД “не известна” клиенту. В этом случае, если версия клиента 8.1.6 или выше, конвертация выполняется на стороне сервера, или попытка открыть сессию закончится ошибкой, если версия клиента ниже 8.1.6.

Окружение и реестр

Кроме переменной NLS_LANG, в реестре (для Windows-платформ) либо в окружении (для всех платформ) можно устанавливать и многие дополнительные NLS-параметры (см. ниже), за исключением NLS_LANGUAGE и NLS_TERRITORY, которые, в действительности, являются составными частями переменной NLS_LANG и поэтому игнорируются, будучи выставленными по отдельности.

Еще один малоизвестный факт: значения других NLS-параметров принимаются во внимание, только если присутствует переменная NLS_LANG.

Значения, установленные в окружении, имеют приоритет над реестром и используются для всех Oracle Home на компьютере. В Windows - это “Переменные Среды” (My Computer -> Properties -> Advanced -> Environment Variables). Нетрудно убедиться, что значение NLS_LANG, установленное здесь, будет иметь приоритет над реестром и для текстового, и для графического SQL*Plus.

Вследствие произошедшего в последние годы усложнения структуры ветви реестра HKLM\Software\Oracle, больше всего сомнений вызывает вопрос, в какой именно подветви реестра следует устанавливать NLS-переменные. Особенно много проблем вызывает установка с несколькими Oracle Home.

Прежде всего, нужно знать, из какого Oracle Home запускается используемое клиентское приложение или утилита. Далее, в поддиректории Bin этого Oracle Home следует отыскать файл oracle.key. Если такой файл найдется, то он и содержит имя ветви реестра, соответствующей этому Oracle Home. Обычно это имя вида HKLM\Software\Oracle\Homex, где x – целое число 0, 1, 2 и т.п. Наличие файла oracle.key характерно для версий Oracle, начиная с Oracle8i. Если файл oracle.key в поддиректории Bin отсутствует, значит, этот Oracle Home принадлежит более старой версии, которая использует непосредственно ветвь HKLM\Software\Oracle.

NLS-параметры инициализации

Файл параметров инициализации может включать около 15 NLS-параметров. Основными и минимально необходимыми являются NLS_LANGUAGE и NLS_TERRITORY. Большинство остальных параметров выводятся из этих двух. Кратко опишем NLS-параметры.

Параметр	Описание
NLS_LANGUAGE	Определяет язык сообщений, названий дней недели и месяцев, порядок сортировки и т.п.
NLS_TERRITORY	Определяет форматы даты и времени, символьное представление чисел, символы валюты, используемую календарную систему и т.п.
NLS_DATE_LANGUAGE	Переопределяет язык названий дней недели и месяцев, если нужен язык, отличный от заданного параметром NLS_LANGUAGE
NLS_DATE_FORMAT	Переопределяет формат даты, если нужен формат, отличный от заданного параметром NLS_TERRITORY
NLS_TIMESTAMP_FORMAT	Переопределяет формат типа timestamp, если нужен формат, отличный от заданного параметром NLS_TERRITORY
NLS_TIMESTAMP_TZ_FORMAT	Переопределяет формат типа timestamp с временной зоной, если нужен формат, отличный от заданного параметром NLS_TERRITORY
NLS_CALENDAR	Переопределяет используемую календарную систему, если нужен календарь, отличный от заданного параметром NLS_TERRITORY. Определение календаря включает в себя такие характеристики, как первый день недели (воскресенье или понедельник), первая неделя года, первый год эры, количество дней в месяце и году и т.п.
NLS_NUMERIC_CHARACTERS	Переопределяет символ-разделитель десятичной части и символ-разделитель групп, если нужны символы, отличные от заданных параметром NLS_TERRITORY
NLS_CURRENCY	Переопределяет символ местной валюты, если нужен символ, отличный от заданного параметром NLS_TERRITORY
NLS_ISO_CURRENCY	Переопределяет символ местной валюты, установленный стандартом ISO, если нужен символ, отличный от заданного параметром NLS_TERRITORY
NLS_DUAL_CURRENCY	Переопределяет символ второй местной валюты (например, Евро), если нужен символ, отличный от заданного параметром NLS_TERRITORY
NLS_SORT	Определяет порядок сортировки символьных строк для ORDER BY. По умолчанию – лингвистический порядок сортировки, соответствующий языку, заданному параметром NLS_LANGUAGE. Может также принимать значения Binary и Punctuation (подробнее см. Особенности сортировки)
NLS_COMP	Определяет способ сравнения символьных строк во фразах и операторах WHERE (=, >, < и т.д.), START WITH, IN, BETWEEN, CASE WHEN, HAVING. По умолчанию – Binary, т.е. в соответствии с двоичными кодами символов. Значение ANSI включает порядок сравнения, указанный параметром NLS_SORT: лингвистический или Punctuation
NLS_LENGTH_SEMANTICS	Определяет способ задания длины символьных столбцов. По умолчанию – Byte, т.е. VARCHAR2(10) означает 10 байт. Значение Char меняет трактовку с байт на символы. Не влияет на уже существующие столбцы, только на новые.
NLS_NCHAR_CONV_EXCP	Определяет реакцию на потерю данных при явном или неявном преобразовании символьных данных. По умолчанию – False, т.е. ошибка не возвращается

Самым важным применением этих параметров является то, что параметр NLS_LANGUAGE определяет язык системных сообщений утилит, ошибок в alert-файле и файлах трассировки. Однако, в некоторых случаях (например, для ошибки ORA-00600) Oracle пишет сообщения об ошибках на английском языке, не обращая внимания на значение параметра NLS_LANGUAGE.

Недокументированный факт: На Windows-платформах язык ошибок в alert-файле и файлах трассировки определяется не параметром инициализации NLS_LANGUAGE, а переменной NLS_LANG.

При определенных условиях эти параметры могут повлиять и на соответствующие установки клиентской сессии (см. Формирование NLS-параметров сессии).

Если значения параметров NLS_LANGUAGE или NLS_TERRITORY не заданы явно, их значения по умолчанию – American и America соответственно. Если значения других NLS-параметров не заданы явно, в большинстве случаев они выводятся из NLS_LANGUAGE и NLS_TERRITORY, но при этом явно не видны в представлениях V$Parameter и NLS_Instance_Parameters.

Особенности сортировки

Каждый из возможных порядков сортировки, задаваемых параметром NLS_SORT, имеет свои особенности.

Так, при лингвистической сортировке прописные и строчные буквы, например A и a, имеют соседние позиции, причем прописная буква (верхний регистр) при сортировке по возрастанию меньше строчной (нижний регистр обычной клавиатуры). Небуквенно-цифровые символы при лингвистической сортировке трактуются особым образом: они как бы опускаются при сортировке. Например, порядок лингвистической сортировки будет такой: a+a, a-a, aa, a+b,…

При значении Binary сортировка выполняется в соответствии с двоичными кодами символов.

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

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

Начиная с Oracle10g, к имени лингвистической сортировки можно добавлять _CI (регистро-независимая сортировка) и _AI (регистро-независимая и accent-insensitive сортировка).

Оператор ALTER SESSION

Оператор ALTER SESSION позволяет во время существования сессии изменить любой из ее NLS-параметров. Нельзя изменить только кодовую страницу сессии, так как это исключительно клиентский параметр: конвертация данных из одной кодовой страницы в другую выполняется на клиенте. Также нельзя изменить значение NLS_LANG, так как это не параметр сессии.

Формирование NLS-параметров сессии

При создании сессии клиент Oracle считывает из реестра и/или окружения значения переменной NLS_LANG и других NLS-параметров, если они присутствуют. При этом применяются следующие правила:

1. учитываются только значения из ветки реестра, соответствующей Oracle Home клиентского инструментального средства или клиентского ПО
2. значения, установленные в окружении, имеют более высокий приоритет, чем значения, установленные в реестре
3. значения других NLS-параметров принимаются во внимание, только если присутствует переменная NLS_LANG
4. если переменная NLS_LANG отсутствует, язык и территория берутся из соответствующих параметров инициализации экземпляра.

По считанным значениям клиентское ПО Oracle формирует и выполняет операторы

ALTER SESSION SET NLS_LANGUAGE=<Язык>;
ALTER SESSION SET NLS_TERRITORY=<Территория>;
...

Эти операторы можно увидеть в файле трассировки сессии.

NLS-параметры, не установленные описанным образом, выводятся. Так, NLS_DATE_LANGUAGE и NLS_SORT, если они не заданы явно, выводятся из NLS_LANGUAGE. NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTERS, если они не заданы явно, выводятся из NLS_TERRITORY.

Таким образом, формируется начальное “NLS-состояние” сессии. В дальнейшем значение любого из NLS-параметров сессии, кроме кодовой страницы и самой переменной NLS_LANG, можно изменить в пределах сессии оператором ALTER SESSION.

Информация об NLS-параметрах

Информацию об NLS-параметрах можно получить из следующих системных представлений:

• NLS_Session_Parameters – NLS-параметры текущей сессии
• NLS_Instance_Parameters – NLS-параметры экземпляра, значения соответствующих параметров инициализации на момент старта экземпляра
• V$NLS_Parameters – то же, что и NLS_Instance_Parameters, но добавлены основная и национальная кодовые страницы БД
• NLS_Database_Parameters – NLS-параметры экземпляра, установленные в файле параметров инициализации (не в NLS_LANG!), на момент создания БД (если не были установлены, то по умолчанию принимается American_America), добавлены основная и национальная кодовые страницы БД. Эти установки используются при проверке ограничений целостности CHECK, если в условиях используются функции TO_CHAR или TO_DATE без явного указания формата.
• V$NLS_Valid_Values – поддерживаемые данной версией СУБД значения NLS-параметров.

Кодовую страницу клиентской сессии, установленную переменной NLS_LANG, нельзя увидеть ни в какой системной таблице, так как это исключительно клиентский параметр.

DOS-окно

Для пользователей, работающих в ОС семейства Windows, особенно для тех, кто использует утилиты Export, Import или SQL*Loader, отдельной проблемой является кодировка DOS-окна. По умолчанию, графические средства используют кодовую страницу CL8MSWIN1251, в то время как DOS-окно – кодовую страницу RU8PC866. Для этой проблемы существуют следующие решения:

• для каждой утилиты, запускаемой в DOS-окне, создать командный файл, в котором перед вызовом собственно утилиты принудительно устанавливать переменную окружения NLS_LANG с кодовой страницей RU8PC866
• изменить кодовую страницу DOS-окна, для чего установить в реестре, в ключе HKLM\System\CurrentControlSet\Control\Nls\CodePage переменную OEMCP в значение 1251 и перезапуститься (аналогично переменная ACP определяет кодовую страницу графических приложений). Это решение имеет некоторые побочные эффекты, в частности, NET SEND начинает “путать” кодировки при пересылке сообщений между компьютерами с разными установками OEMCP.

Кроме того, нужно установить для DOS-окна фонт, поддерживающий кириллицу. Таким фонтом может быть, например, Lucida Console. Процедура смены фонта:

1. кликнуть правой клавишей мыши на заголовке DOS-окна
2. выбрать пункт меню Properties
3. выбрать вкладку Font
4. выбрать фонт (Lucida Console)
5. нажать OK
6. выбрать Modify shortcut which started this window.

Установка фонта в SQL*Plus (GUI)

Фонт, используемый в графическом интерфейсе SQL*Plus, определяется двумя переменными реестра (или окружения): SQLPLUS_FONT и SQLPLUS_FONT_SIZE. Установленный фонт должен быть моноширинным (Courier New, Terminal, Lucida Console). Если в качестве фонта выбран один из пропорциональных фонтов, или введены некорректные значения, будет использоваться фонт по умолчанию – Fixedsys, 16. Если введен недоступный размер фонта, будет использоваться размер по умолчанию – 16.

Интересно, что при изменении этих переменных в реестре не обязательно перезапускать SQL*Plus.

Олег Летаев,
oletaev@leaves.ru ,
Oracle Certified Professional