-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|