Oracle设置默认的NLS_LANG [英] Oracle set up default NLS_LANG

问题描述

我正在RHEL6上运行Oracle数据库11g.如果没有设置客户端NLS_LANG，则某些utf8字符的长度为2.设置NLS_LANG = AMERICAN_AMERICA.UTF8后，长度仅为1. 怎么能更改整个数据库的默认NLG_LANG?我不想只为会话更改此值，或者不想像linux中的环境变量一样更改此值.有可能将其设置为数据库通用吗?

I am running Oracle database 11g on RHEL6. If is no client NLS_LANG set the length of some utf8 character is 2. After NLS_LANG=AMERICAN_AMERICA.UTF8 is set the length is only 1. How can bet the default NLG_LANG changed for whole database? I don't want to change this value only for session, or like enviromental variable in linux. Is there any possibility to set it general for database?

SQL> select length('á') from dual;

LENGTH('??')
------------
           2

# export NLS_LANG=AMERICAN_AMERICA.UTF8

SQL> select length('á') from dual;

LENGTH('á')
-----------
          1

非常感谢您提出任何想法

Many thanks for any idea

推荐答案

这可能会发生:

• 您的客户端字符集必须类似于CP1252或ISO-8859-15，而实际上您的客户端确实使用UTF8.
• 在此字符集(UTF8)中，符号á占用两个字节，因此您的客户端发送了这两个字节，同时告诉Oracle将它们视为CP1252.在CP1252中，两个字符的两个字节代码导致DB将输入解释为两个字符，因此length('à')等于2(并且如果插入此字符串，则插入结果不等于à)
• 正确设置字符集后，oracle会将输入正确地视为单个字符，并且其长度为1(仍为两个字节).

• Your client character set must be something like CP1252 or ISO-8859-15 whereas in fact your client really uses UTF8.
• In this character set (UTF8), the symbol á takes two bytes, so your client send these two bytes, while telling Oracle to treat these as CP1252. In CP1252 the two bytes code for two characters which result in the DB interpreting the input as two characters, therefore length('à') equals 2 (and if you insert this string the result of the insert is not equals to à)
• When you correctly set the character set, the input is correctly treated by oracle as a single character, and its length is 1 (still two bytes).

结论:正确设置客户字符集，否则会出现翻译错误(您不会以这种方式获取非法字符，但可能会得到奇怪的符号(¿).

Conclusion: set your client character set properly or you will get translation errors (you won't get illegal characters this way but you may get strange symbols (¿).

数据库字符集是在创建时设置的，通常通过导出/创建空白数据库/导入来更改.

The database character set is set at the time of creation and is generally changed via export/create blank database/import.

这篇关于Oracle设置默认的NLS_LANG的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！