import where char set is different - ORA-01461

[Luch]

One of our customers sent us an export dmp which we're trying to
import. Their database uses a character set that is West European or
MSWIN, whereas ours is Unicode. During the import, we get this error:

. . importing table "DEFECT_REPORT"
IMP-00058: ORACLE error 1461 encountered
ORA-01461: can bind a LONG value only for insert into a LONG column
IMP-00018: partial import of previous table completed: 614870 rows
imported

The table contains two columns that are varchar2(4000). We believe
this, along with the difference in character set, is the source of the
problem. We think there are certain records that have data, that in a
certain char set, are "too big" to fit into the same column once the
database is in another character set.

The question is, is this assessment correct? And if so, what are the
right steps to correct this, if I want to keep my database as Unicode?
Should I try to look for the records that are causing this problem,
and "truncate" them prior to the customer doing the export, etc?

[Carlos]

Luch ha escrito:

WE8ISO8859 or MSWIN use 1 byte per character. When you translate the
Varchar2 into UNICODE UTF8 some of the characters use 2 bytes per
character (i.e.: Ñ). If the original column is 4000 bytes long, the
translation makes it longer than 4000 bytes, and then there is no room
in a Varchar2 column.

HTH.

Cheers.

Carlos.

[david]

Is the varchar2 column defined as varchar2(4000 byte) or varchar2(4000
char)? This may be the reason the data will not fit. Using
varchar2(4000 char) will allow for 4000 characters. As already stated,
the UTF-8 encoding will use multiple bytes to represent characters
higher than U+007F.

Dave

[Laurenz Albe]

Luch <Dart...@gmail.com> wrote:
> One of our customers sent us an export dmp which we're trying to
> import. Their database uses a character set that is West European or
> MSWIN, whereas ours is Unicode. During the import, we get this error:
>
> . . importing table "DEFECT_REPORT"
> IMP-00058: ORACLE error 1461 encountered
> ORA-01461: can bind a LONG value only for insert into a LONG column
> IMP-00018: partial import of previous table completed: 614870 rows
> imported
>
>
> The table contains two columns that are varchar2(4000). We believe
> this, along with the difference in character set, is the source of the
> problem. We think there are certain records that have data, that in a
> certain char set, are "too big" to fit into the same column once the
> database is in another character set.
>
> The question is, is this assessment correct?

Unless the error message is misleading, I would take it to mean that
the import tries to insert a LONG column into a database field of
a different type.

Is the database into which you are trying to import empty? If not, an
existing table definition might not match the imported data.

But maybe the message is misleading and it is indeed a character length
semantics problem.

Start with an empty database.

You could first import only the table definitions (using ROWS=n) and
then change all character fields to length semantics CHAR.

After that, you can import a second time using IGNORE=y and use the
modified tables. Maybe that will solve your problem.

Yours,
Laurenz Albe