Re: import error 1461 with oracle 8.1.6

From: Mike Liu <mike2322_at_hotmail.com>
Date: 23 Jan 2003 11:42:17 -0800
Message-ID: <2262aa8e.0301231142.74a182f1_at_posting.google.com>


"Samuel Tribehou" <s.tribehou_at_wanadoo.fr> wrote in message news:<b0ou4p$2pk$1_at_news-reader11.wanadoo.fr>...
> First sorry for the crosspost, but i'm really in a hurry and I don't really
> know which groups this post belongs to, since I don't know what the problem
> is...
>
> What I'm trying to do is simply to export/import a database from 8.1.6/NT4
> to 8.1.6/AIX. The problem is, the dump file works fine if I reimport on the
> NT database, but it will fail with error 1461 on AIX.
> Our client (whom the AIX system belongs to) says he already imported similar
> dumps with no problems ( made with NT4/8.1.6, and with similar data (i.e it
> is for
> the same application, so there are alot of fields which are identical))
>
>
> Here is what happens :
>
> --
> [bidon_at_oracle]-/home/oracle-> import file=file.dmp fromuser=dgos
> touser=dgos tables=vc_generic_operations;
>
> Import: Release 8.1.6.0.0 - Production on Tue Jan 21 12:31:47 2003
>
> (c) Copyright 1999 Oracle Corporation. All rights reserved.
>
>
> Connected to: Oracle8i Release 8.1.6.0.0 - Production
> JServer Release 8.1.6.0.0 - Production
>
> Export file created by EXPORT:V08.01.06 via conventional path
> import done in WE8ISO8859P1 character set and UTF8 NCHAR character set
> import server uses UTF8 character set (possible charset conversion)
> export server uses WE8ISO8859P1 NCHAR character set (possible ncharset
> conversion)
> . importing DGOS's objects into DGOS
> . . importing table "VC_GENERIC_OPERATIONS"
> IMP-00058: ORACLE error 1461 encountered
> ORA-01461: can bind a LONG value only for insert into a LONG column
> IMP-00028: partial import of previous table rolled back: 1768 rows rolled
> back
> About to enable constraints...
> Import terminated successfully with warnings.
> --
>
> The values which import hangs on are varchar2(4000), not longs. I believe
> the size is causing a problem here. Most fields in this tables are really
> 4000 chars long.
>
> So since I couldn't find a way to get this working, I decided to make a new
> dump with no data
> in the tables which cause problems, then create an acsii script which would
> fill the tables...
> No luck there too. I have an ORA-01489 error when I try to create the
> script :
>
> > select filed1||','||field2 from vc_generic_operations;
> ORA-01489: result of string concatenation is too long
>
> Anyone knows a way to get around any of those problems ?
> Please try to be precise in your answers, I'm not an Oracle expert..

[Quoted] 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 Received on Thu Jan 23 2003 - 20:42:17 CET

Original text of this message