How can I truncate data to fit into a field using SQL*Loader? (ORA-12899)

Question

Using Oracle SQL*Loader, I am trying to load a column that was a variable length string (lob) in another database into a varchar2(4000) column in Oracle. We have strings much longer than 4000 characters, but everyone has agreed that these strings can and should be truncated in the migration (we've looked at the data that goes beyond 4000 characters, it's not meaningful). To do so, I specified the column this way in the control file:

COMMENTS CHAR(65535) "SUBSTR(:COMMENTS, 1, 4000)",

However, SQL*Loader still rejects any row where this record is longer than 4000 characters in the data file:

Record 6484: Rejected - Error on table LOG_COMMENT, column COMMENTS. ORA-12899: value too large for column COMMENTS (actual: 11477, maximum: 4000)

Record 31994: Rejected - Error on table LOG_COMMENT, column COMMENTS. ORA-12899: value too large for column COMMENTS (actual: 16212, maximum: 4000)

Record 44063: Rejected - Error on table LOG_COMMENT, column COMMENTS. ORA-12899: value too large for column COMMENTS (actual: 62433, maximum: 4000)

I tried taking a much smaller substring and still got the same error. How can I change my control file to truncate string data longer than 4000 characters into a varchar2(4000) column?

Answer 1

Check to make sure your data ENCODING and Oracle ENCODING are not conflict. In this case, use CHARACTERSET option when loading.