2

I want to insert Chinese characters in Oracle database.

select length('有个可爱的小娃在旁边') from dual;
10
drop table multibyte;
create table multibyte (name varchar2(10));
insert into multibyte
values('有个可爱的小娃在旁边');

I get a error message saying 

           An attempt was made to insert or update a column with a value
           which is too wide for the width of the destination column.
           The name of the column is given, along with the actual width
           of the value, and the maximum allowed width of the column.
           Note that widths are reported in characters if character length
           semantics are in effect for the column, otherwise widths are
           reported in bytes

I know that if i increase the column width the problem can be wished away. My question is when the length function tells me the width is 10 why cant i insert it into a column which is of varchar2(10) ?

jhon.smith
  • 1,963
  • 6
  • 30
  • 56

2 Answers2

6

The difference is due to the definition of the column: VARCHAR2(10) is equivalent to VARCHAR2(10 BYTE), whereas what you want is VARCHAR2(10 CHAR).

Difference between BYTE and CHAR in column datatypes

Community
  • 1
  • 1
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • 1
    Just to clarify: regardless of which definition is chosen, the limit is 4000 bytes (or 32k for extended data types on Oracle 12c) – Alfabravo Jan 26 '18 at 16:00
1

Yeah, that is a bit unfortunate. Oracle measures the text column length in bytes, so a varchar2(10) can only store ten bytes, which is about three Chinese characters.

Thilo
  • 257,207
  • 101
  • 511
  • 656