?
Solved

multi-byte encoding --- VARCHAR2(500 BYTE) vs VARCHAR2(500 CHAR)

Posted on 2012-08-17
12
Medium Priority
?
2,989 Views
Last Modified: 2016-09-02
Hi Experts,

I have database uses a UTF8 characterset and all columns for taking user input for all text area use this:

VARCHAR2(500 BYTE)


When user enters Chinese, I doubt those columns do not take 500 Chinese characters.
(I added javascript validation user can only enter less than 500 char in those textarea.)

Does it have to be VARCHAR2(500 char) instead of VARCHAR2(500 BYTE)?

thanks
0
Comment
Question by:dkim18
  • 6
  • 5
12 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38306391
I believe you want CHAR.  My multi-byte expierence is minimal.  I would set up a quick test and test it.  Create a table both ways and see how many you can store.

http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch2charset.htm#i1006683

Length Semantics

For example, in a Unicode database (AL32UTF8), suppose that you need to define a VARCHAR2 column that can store up to five Chinese characters together with five English characters. Using byte semantics, this column requires 15 bytes for the Chinese characters, which are three bytes long, and 5 bytes for the English characters, which are one byte long, for a total of 20 bytes. Using character semantics, the column requires 10 characters.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38307943
Yes, as mentioned by slightwv, you need 500 characters, not 500 bytes. Another suggestion would be to migrate the database character set to AL32UTF8 since UTF8 is deprecated or has been superseded by AL32UTF8
0
 

Author Comment

by:dkim18
ID: 38312503
Thanks for your comments.
All columns that take less than 1000 char works fine.
However, there is one column takes user comment(4000 char) and I tried enter 2000 Chinese character and got this error.

ORA-01461: can bind a LONG value only for insert into a LONG column

Any idea?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dkim18
ID: 38312510
This happened after all the VARCHAR2 columns have been changed to VARCHAR2( nn CHAR)
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38312548
>>ORA-01461: can bind a LONG value only for insert into a LONG column

Sorry but I'm not an Expert on multi-byte character sets.  This appears to be an app issue and how it is declaring the variables being passed into Oracle.

>>and I tried enter 2000 Chinese character and got this error.

Is our Chinese characterset 2 bytes per letter or 3?  I remember reading somewhere they could be either.
0
 

Author Comment

by:dkim18
ID: 38313014
It looks like 3 bytes if you take a look figure 2-2 in the following link.

http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch2charset.htm#i1006683
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38313044
If your chinese characters are 3 bytes per character then you cannot add 2000 of them to a varchar2(4000).  Only 1333.
0
 

Author Comment

by:dkim18
ID: 38313052
FYI, if I enter 1000 Chinese characters, I don't see this error. (it works fine.)
0
 

Author Comment

by:dkim18
ID: 38313071
after converting all VARCHAR2( nn BYTE) to VARCHAR2( nn CHAR), I was able to insert same number of Chinese characters compare to English.
ex)
For VARCHAR2( 100 CHAR) columns, I can enter 100 Chinese characters.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 38313077
>>FYI, if I enter 1000 Chinese characters, I don't see this error. (it works fine.)

I belive the max is 1333.

>>For VARCHAR2( 100 CHAR) columns, I can enter 100 Chinese characters.

That is because it is under the 4000 max for a single varchar2.
0
 

Author Comment

by:dkim18
ID: 38313231
ah...I might be misunderstanding here.
So, it doesn't matter if I modify a column to VARCHAR2( nn BYTE) or VARCHAR2( nn CHAR)? VARCHAR2 can only take 4000 bytes max?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38313547
>>VARCHAR2 can only take 4000 bytes max?

http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#SQLRF50977

...
Independently of the maximum length in characters, the length of VARCHAR2 data cannot exceed 4000 bytes.
...
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
The viewer will learn how to implement Singleton Design Pattern in Java.
Suggested Courses
Course of the Month16 days, 13 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question