How can I truncate data to be inserted into a field using SQL * Loader? (OR-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 lines longer than 4000 characters, but everyone agrees that these lines can and should be truncated during the transfer (we looked at data exceeding 4000 characters, this does not make sense). To do this, I indicated the column in this way in the control file:

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

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

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

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

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

I tried to make a much smaller substring and still got the same error. How to modify a control file to trim string data longer than 4000 characters in a varchar2 (4000) column?

Answer 1

Make sure your ENCODING and Oracle ENCODING data do not conflict. In this case, use the CHARACTERSET option at boot time.

Answer 2

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

- . sqlldr 11.2.0.1, , > 4000,

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

directpath, smae .

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

2- . CLOB,

COMMENTS CHAR(2000000000) 

eth

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

,