DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: NLS_LENGTH_SEMANTICS and VARCHAR2 field length question

  1. #1
    Join Date
    Nov 2006

    NLS_LENGTH_SEMANTICS and VARCHAR2 field length question

    I have a database with NLS_LENGTH_SEMANTICS set to BYTE, NLS_NCHAR_CHARACTERSET is AL16UTF16 and NLS_CHARACTERSET is UTF8. I have a program that is writing data to a field defined as VARCHAR2 (234).

    I am writing simplified Chinese characters to the field . When I write the data I get an error message:
    ORA-12899 Value too large for column: actual 275, max 234

    The # of Chinese characters in the string is 71. I thought with UTF8 I would require a maximum of 3 bytes per character (non supplemental characters) - so I would expect the actual to be 71*3 or no bigger than 213 bytes.

    Why is the message telling me the actual is 275 bytes?

    How can I calculate the max size I need in bytes if I know the max number of Chinese characters.

    This is a large production database where only a few records will be populated with Chinese characters - so we would prefer to keep the semantics to BYTE rather than CHAR (does it make a difference in space utilized if we change to CHAR and most of the data is in english?)
    Last edited by MartyRL; 07-26-2011 at 09:19 PM.

  2. #2
    Join Date
    Mar 2007
    Ft. Lauderdale, FL
    is your NLS_LANGUAGE set to SIMPLIFIED CHINESE? if not, how can you be sure nothing else is going in?

    Don't do CHAR, that will make your columns fixed lenght.

    For a quick, dirty and safe solution stay VARCHAR2 and assign a safe lenght, go 500 - who cares? VARCHAR2() is a variable size data type that will allocate exactly what is needed and not a single extra byte -other than data type overhead, off course.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width