"richie" <richchri_at_erols.com> wrote in message
news:9a436674.0307010624.56fa4313_at_posting.google.com...
> 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.
Forget SUBSTR, it's too late, the field is already bound and it's too large
to fit, write the statement this way:
(fldDescription POSITION(1:4000))
You don't need either CHAR(4000), is derived from POSITION.
By the way, without the SUSBTR, you can use Direct path load, much faster.
Bye,
Flavio
Received on Wed Jul 02 2003 - 05:41:53 CDT
