木匠的微型博客 Charlie Twitter

    follow me on Twitter

    Tuesday, January 22, 2008

    Supporting Multilingual Databases and NLS_CHARACTERSET

    We recommend going to AL32UTF8 as the ultimate solution for Oracle 11g-.
    AL32UTF8 is the database character set that supports the latest version (5.0 in Oracle 11.1) of
    the Unicode standard. It also provides support for the newly defined supplementary characters.

    Here are some major points I briefed as a reference.

    How to move to AL32UTF8 / UTF8 (Unicode) Database Character Set
    Note:119119.1

    to check you database Character Set,
    select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

    Usualy database will grow when going to AL32UTF8, use CSSCAN to generate the size expansion report.

    The NLS_LENGTH_SEMANTICS initialization parameter determines whether a new column of character datatype uses byte or character semantics. The default value of the parameter is BYTE. The BYTE and CHAR qualifiers shown in the VARCHAR2 definitions should be avoided when possible because they lead to mixed-semantics databases. Instead, set NLS_LENGTH_SEMANTICS in the initialization parameter file and define column datatypes to use the default semantics based on the value of NLS_LENGTH_SEMANTICS.

    columne_name VarChar2(300 char/byte)

    Related function: lengthb(), substrb()

    UniStr() over Chr()
    select Chr(163) from dual;
    select UniStr('\C2A3') from dual;

    convert(string_column,'AL32UTF8','US7ASCII'), convert from US7ASCII to AL32UTF8.

    To use WE8MSWIN1252 over WE8ISO8559P1, WE8MSWIN1252 supports European Code.

    Reference

    * US7ASCII: US 7-bit ASCII character set
    * WE8ISO8859P1: ISO 8859-1 West European 8-bit character set
    * WE8MSWIN1252: Microsoft Windows West European Code Page 1252
    * UTF8: Unicode 3.0 Universal character set CESU-8 encoding form
    * AL32UTF8: Unicode 5.0 Universal character set UTF-8 encoding form

    **Unicode character sets in the Oracle database, Note:260893.1

    exp/imp

    set NLS_LANG=
    export

    set NLS_LANG=
    import into the new UTF8 db.

    The conversion to UTF8 is done while inserting the data in the UTF8 database.

    Today's learning:

    如果你确信了那些消极的结论,那么你就极少可能去冒险和尝试新事物。消极的想法掩盖了你的感受,使其处于恐惧中。

    积极的想法的也是以同样的方式起到其作用。带着积极的心态,你将搜寻那些积极的选择然后确信其积极的结论。它帮助你克服恐惧并且尝试那些可能你认为“不能做到”的事情.

    积极的思想,有助于确定目标实现.

    No comments: