Thanks for the question, Lev.
Asked: September 09, 2004 - 10:31 am UTC
Last updated: September 09, 2004 - 12:28 pm UTC
Version: 8.1.7
Viewed 1000+ times
You Asked
Hi, Tom
Thanks for a great site.
I have 2 databases:
1 v8.1.7 character set - WE8MSWIN1252
2 v8.1.7 character set - UTF8
In the first database there is a table T:
create table T
(
ID NUMBER,
NAME VARCHAR2(4000)
)
it is populated with the data:
insert into t
(id,name)
values
(1, rpad(chr(233),4000,chr(233)))
in the second database there is a table:
create table T
(
ID NUMBER,
NAME CLOB
)
I want to copy data from the first database to the second
insert into T@dblink_from_first_to_second
(id,name)
select id,name from t
ORA-01401: inserted value too large for column
ORA-02063: preceding line from dblink_from_first_to_second
Why it happens? How can I copy this data?
Thanks,
Lev
and Tom said...
WE8MSWIN1252 is a single byte character set.
UTF8 is a multibyte character set - a single character can take 1, 2, 3, 4 or more bytes.
a varchar2(4000) can only hold 4000 bytes. Period -- maximum.
a chr(233) in WE8MWSIN1252 takes a single byte. In UTF8 that character apparently takes MORE than one byte (insert into your table select substr(c,1,1) and use DUMP on the results -- you'll see)
So, what that means is -- this copy isn't going to happen, you'll need to use a CLOB in order to store this 4000 character piece of data since it takes much more than 4000 bytes to hold it.
In Oracle9i, you can actually create tables using:
...
c varchar2(20 CHAR),
.....
to state you want to hold 20 characters (meaning the field isn't limited to 20 BYTES, but 20 CHARACTERS which could be as much as 120 bytes!). However, the 4000 byte limit holds true for varchar2 -- even if you:
c varchar2(4000 CHAR),
.....
it'll still only hold 4000 BYTES at most which could be as few as 666 characters.
It is the nature of multi-byte character sets.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment