This discussion is archived
1 Reply Latest reply: Jul 7, 2011 5:50 AM by Udo RSS

db link, cannot copy from charset WE8ISO8859P1 to AL32UTF8 without loss

Alain Newbie
Currently Being Moderated
Hi,

source is 10.2.0.3.0 enterprise edition database, database uses WE8ISO8859P1 characterset, table is
SQL> desc cec.softtypes
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(9)
DESCRIPTION NOT NULL CHAR(30)
PRODUCTION NOT NULL CHAR(1)
COMMENTAIRE NOT NULL CHAR(254)

create database link abtest connect to ... identified by ... using 'ip:1521/XE'.
This link points to 11g xe april beta 2011 with a database characterset is AL32UTF8.
I have the same table in xe.

Both db use american language, american territory.

SQL> insert into cec.softtypes@abtest select * from cec.softtypes;
insert into cec.softtypes@abtest select * from cec.softtypes
*
ERROR at line 1:
ORA-12899: value too large for column "CEC"."SOFTTYPES"."COMMENTAIRE" (actual:
255, maximum: 254)
ORA-02063: preceding line from ABTEST

I can insert substr(description,1,250).

So it means I have to drop characters. I suppose characterset conversion doesn't occur.

Regards,
Alain.
  • 1. Re: db link, cannot copy from charset WE8ISO8859P1 to AL32UTF8 without loss
    Udo Guru
    Currently Being Moderated
    Hi Alain,
    I suppose characterset conversion doesn't occur.
    I suppose it does occur, otherwhise the strings would use the same amount of bytes. Note that in AL32UTF8, some (special) characters use more than one byte, so you might need more space to store the same value. Of course, you gain the advantage to be able to store (nearly) any special character of (nearly) any charset.
    So, if you want to avoid that problem, don't define your columns with byte length (which is default) but with CHAR-length, e.g.
    DESCRIPTION NOT NULL CHAR(30 CHAR)
    That way, you'll always be able to store 30 characters with respect to the needs of your charset.

    -Udo

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points