sqlldr ORA-01461

Running Oracle 9.2.0.1.0 on Redhat Advanced Server. Trying to use sqlldr to populate table. Data file has a field over 4000 char. I have a SUBSTR in the sqlldr control file that says take the first 4000 char. there is a line
flddescription CHAR(4000) "SUBSTR(:flddescription, 1, 4000). The CHAR(4000) was the tell sqlldr that that field can be longer then 255.  If I try to increase that value beyond 4000 then I get ORA-01461: can bind a LONG value only for insert into a LONG column. I realize a varchar2 max value is 4000 char. However, I want to simply read the field and take only the first 4000 char from the data file. Why can't I do this? If I change the CHAR(4000) to a long in the control file (I can't change the field in the table it needs to be a varchar2) I of course get other errors.

Anyway, long story short. I want to read a data file that has a feild over 4000 char and take only the 1st 4000 and stick it into a varchar2. Yes, I can preprocess the file but don't want to.

Below is control file (names have been changed to protect the innocent)

OPTIONS (ROWS=10000)
LOAD DATA
INFILE 'test.txt' "STR X'0d0a'"
BADFILE 'test.bad'
DISCARDFILE 'test.dsc'
APPEND
INTO TABLE test
FIELDS TERMINATED BY X'09' TRAILING NULLCOLS (
fldDescription CHAR(4000) "SUBSTR(:fldDescription, 1, 4000)" )

Anyone?....thanks. Received on Tue Jul 01 2003 - 09:24:31 CDT