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

by all accounts

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

is the correct syntax. using sqlldr 11.2.0.1 it works successfully for me up until the point where the input record column is > 4000 where i get a

ORA-01461: can bind a LONG value only for insert into a LONG column

if i switch to a directpath load then i get the smae error as you.

ORA-12899: value too large for column COMMENTS (actual: 4005, maximum: 4000)

in the end i have split it into a 2 stage load.. i now have a staging table with a column of type CLOB which i load with

COMMENTS CHAR(2000000000) 

which then gets inserted to eth main table with a

insert into propertable
select dbms_lob.substr(comments,1,4000)
from staging_table;

hope thats helpful

Answer 2

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