Re: import error 1461 with oracle 8.1.6
Date: Sat, 01 Feb 2003 15:48:05 +0100
Message-ID: <3E3BDE25.1010807_at_netscape.net>
Mike Liu wrote:
> "Samuel Tribehou" <s.tribehou_at_wanadoo.fr> wrote in message news:<b0qoob$28j$1_at_news-reader11.wanadoo.fr>... >
>>"Mike Liu" <mike2322_at_hotmail.com> a écrit dans le message de news:
>>2262aa8e.0301231142.74a182f1_at_posting.google.com...
>>
>>>I think the problem is in character set conversion from WE8ISO8859P1
>>>to UTF8. UTF8 uses multi-byte encoding while WE8ISO8859P1 uses
>>>single-byte. For instance, characters in the range of 0x0080 - 0x07ff
>>>(unicode) have two bytes in UTF8.
>>>
>>>SQL> select dump(chr(250), 16) from dual;
>>>
>>>DUMP(CHR(250),1
>>>---------------
>>>Typ=1 Len=1: fa
>>>
>>>
>>>SQL> select dump(convert(chr(250), 'UTF8'), 16) from dual;
>>>
>>>DUMP(CONVERT(CHR(2
>>>------------------
>>>Typ=1 Len=2: c3,ba
>>>
>>>The details of UTF8 encoding is avaiable at
>>>http://www.unicode.org/reports/tr27/.
>>>
>>>In your case, you have some data which are indeed 4000 bytes long in
>>>WE8ISO8859P1 database. When you convert them to UTF8, they will be
>>>longer than 4000 bytes if there is any character in the data that is
>>>in range of 0x0080 and above. That's probably why you got the errors.
>>>
>>>hth,
>>>Mike
>>
>>Ok, thanks for the clear explanation.
>>So I suppose this also means that I can't convert the database to UTF8
>>before dumping it since it will need more than 4000 bytes...
>>Is there a workaround I could use to pass the data to our customer despite
>>this limit ?
>>I suppose the only solution left is to feed the database with a script,
>>but Oracle won't let me concatenate those fields to create a script since
>>concatenations would break the 4000 bytes limit too...
> > > I guess there is no way around this unless you can change the field > type to clob or truncate the data when they are longer than 4000 > bytes. The bottom line is that you can not store more than 4000 bytes > in a varchar2 field. > > Mike
There is...
Make sure the char and nchar charactersets are equal at both sides.
You client and your settings are just opposite. Check at your client
(I think it's WE8ISO8859p1/WE8ISO8859p1), and make your environment
use the same (WE8ISO8859p1, not UTF8). Pay attention to the
"import server uses UTF8" bit.
Bottom line... as soon as you see the "possible conversion" warning, your import will not work.
One other observation: are you importing on pre-built tables? It looks asif VC_GENERIC_OPERATIONS has a column definition changed from LONG to varchar2(4000).
Check with imp ../.. indexfile=blah.txt; then see what the create table statement for this table looks like in blah.txt
Hth, Frank Received on Sat Feb 01 2003 - 15:48:05 CET