This discussion is archived
6 Replies Latest reply: Nov 19, 2012 10:29 AM by Sergiusz Wolicki (Oracle) RSS

NLS_LENGTH_SEMANTICS

936749 Newbie
Currently Being Moderated
Hi,
We're migrating from Western Europe character set to UTF8 and we see that some columns is not "sized" to store the data as some characters now are multi bytes (as expected)
As I see it we basically have 3 options:

1. Do not change character set
2. Increase size of column
3. change NLS_LENGTH_SEMANTICS to char

Alternative 1: is possible but really more of a last way out of this issue.
Alternative 2: is also an option and easy for us DBA's to implement, but it has some big downsides for developers as they may have to change size different places in the application.
Alternative 3: This one I'm a bit unsure of. The default is byte and I guess it's a reason why Oracle has byte's as default? Is there any downside to running char if not why would Oracle use byte and why is there two options?

If someone know of a 4'th and 5'th I'd be grateful for your inputs.

Please share your knowledge :)
  • 1. Re: NLS_LENGTH_SEMANTICS
    Fran Guru
    Currently Being Moderated
    please check:
    http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch11charsetmig.htm#CEGCGEAF
  • 2. Re: NLS_LENGTH_SEMANTICS
    Herald ten Dam Expert
    Currently Being Moderated
    Hi,

    there are pros and cons for option 3. There are notes on Oracle Support about the parameter: doc 144808.1 . There are limitations if you use the char setting.

    There is a 4th, or is it a variation of 2?. You can also change the byte of a column to a char. Effectivily is this also a increase of the size, but a more documented one. In that case you need only to adjust the columns you need, example:
    alter table XXX modify (YYY (20 CHAR)) 
    This latest is the most wide used option. Declare the columns for the need of byte of char, and leave the NLS_LENGTH_SEMANTICS on BYTE.

    Herald ten Dam
    http://htendam.wordpress.com
  • 3. Re: NLS_LENGTH_SEMANTICS
    P.Forstmann Guru
    Currently Being Moderated
    Storing UTF8 character data in some Western European character set will lead to data corruption: it is not only a question of column size but also a question of code points used to store character data.
    You should be very very carefull about this.

    If you want to avoid corrupted data you have only 2 options:
    1. migrate database character set to UTF8 so that all columns using standard character data types such as VARCHAR2 and CLOB can use UTF8 character set
    2. do not change database character set but move all data that must store UTF8 to "N" data types such as NVARCHAR2 and NCLOBs.

    You should read "Choosing a Character Set" from http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch2charset.htm#i1007681.

    It may also be better to use dedicated Globalization Support forum Globalization Support .
  • 4. Re: NLS_LENGTH_SEMANTICS
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl also see this related thread - Need help on changing parameter NLS_LENGTH_SEMANTICS
  • 5. Re: NLS_LENGTH_SEMANTICS
    936749 Newbie
    Currently Being Moderated
    Herald ten Dam wrote:
    Hi,

    there are pros and cons for option 3. There are notes on Oracle Support about the parameter: doc 144808.1 . There are limitations if you use the char setting.

    There is a 4th, or is it a variation of 2?. You can also change the byte of a column to a char. Effectivily is this also a increase of the size, but a more documented one. In that case you need only to adjust the columns you need, example:
    alter table XXX modify (YYY (20 CHAR)) 
    This latest is the most wide used option. Declare the columns for the need of byte of char, and leave the NLS_LENGTH_SEMANTICS on BYTE.

    Herald ten Dam
    http://htendam.wordpress.com
    Hi,
    I think we'll end up with just adding the semantics to column as you've described.
    This will in some cases be a change from BYTE to CHAR. Do you know if we need to change anything within the application?
    Regards
    933746
  • 6. Re: NLS_LENGTH_SEMANTICS
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    Oracle recommendation is as follows:

    1. Do not give up migration to Unicode. Unicode is the recommended database character set.
    2. Migrate to AL32UTF8, not UTF8 (because UTF8 is not UTF-8; it is CESU-8 -- an unfortunate historical naming mess).
    3. Do not use NCHAR (national character set) columns; use standard VARCHAR2 columns with AL32UTF8 (in most situations).
    4. To solve the column length issue, altering the columns to character semantics using ALTER TABLE or CREATE TABLE with explicit character length specification, e.g. name VARCHAR2(50 CHAR), is often a good idea.
    5. Generally, do not change the value of NLS_LENGTH_SEMANTICS in init.ora/spfile to CHAR. Try to not depend on the parameter at all. Explicit semantics specification in CREATE TABLE/PROCEDURE/FUNCTION/... is strongly preferred.

    Regarding application modifications, there is no single answer. The two most important factors determining the required adaptation work are:

    1. Will the application character set be changed to UTF-8 as well or will the application keep working in a single-byte character set (e.g. WE8MSWIN1252)?
    2. What is the programming language and Oracle API in use?


    -- Sergiusz

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points