00626 SQL Loader error

Question

How to avoid

"characterset conversion buffer overflow" error in sql*loader? error # 00626.

I am not able to find this on internet please suggest me the solution for this.

Answer 1

It's not a lot of help, but here's what the Oracle error manual has to say about that error:

SQL*Loader-00626: Character set conversion buffer overflow.

Cause: A conversion from the datafile character set to the client character set required more space than that allocated for the conversion buffer. The size of the conversion buffer is limited by the maximum size of a varchar2 column.

Action: The input record is rejected. The data will not fit into the column.

It sounds like there isn't any way to work around this within SQLLoader. If it is affecting a small number of records then it may be easiest to simply handle those manually. If it is many records, then you probably need to find or create a different loading tool.

Answer 2

Just a few ideas for you to think about:

• You could try to load different parts of the "string" into different fields in the database .. maybe that way you can work around the limitation.
• You could try to do the character set conversion in a different tool .. some text editors may give you some options .. and then load the file without it requiring the conversion.

Not sure if there's any merit in these ideas, but hopefully you can work something out.

Answer 3

What is the character set of the input datafile? You might try specifying the character set in the control file:

CHARACTERSET char_set_name LENGTH SEMANTICS CHARACTER

By default, if not specified, Oracle will use byte length semantics. Thus, if you define a field length in your control file as VARCHAR(20), in byte semantics you'd have 20 byte buffer, but in character length semantics you might have a 40 byte buffer. This would be my guess as to what could be the source of the error.

Answer 4

Thanks for all your help. This problem has been resolved. We split the file and loaded in chunks and it worked fine