Re: SQLLOADER: 255 CHAR limit

Erik,

Thanks for the example. Have you tried this when there is more than one field that is 2000 characters? SQL*Loader handles one "big" field at the end of each record, no problem. In my testing, I could not get it to work with multiple "2000" fields or "2000" fields in the middle each record.

jk

In article <39ca587d.23016211_at_news.bellatlantic.net>,   cohene_at_sdc-moses.com (Erik Cohen) wrote:
> here is a snip of my control file that loads >255 data into a col:
>
> LOAD DATA
> INFILE '/project/edr/data/rcris_template.txt'
> BADFILE '/project/edr/data/rcris_template.bad'
> TRUNCATE
> INTO TABLE BATCH_DATA_LOAD
> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '`'
> TRAILING NULLCOLS
> (
> batch_seq
> ,cd_nm NULLIF (cd_nm = " ")
> ,vd_def_txt CHAR(2000) TERMINATED BY ','
> OPTIONALLY ENCLOSED BY '`'
> )
>
> ignore the line wrap. 255 is the default limit. You override it
> using the CHAR parameter.
>
> On Thu, 21 Sep 2000 14:45:50 GMT, jkokinda_at_raymondkarsan.com wrote:
>
> >Issue: I'm trying to load a delimited text file into an Oracle table
> >with data that execeeds 255 characters and it's not working
 correctly.
> >
> >The table fields are set to VARCHAR2 2000 - 4000. No problem. Some
 of
> >the data fields in the file have over 1000 characters. Ok. When
 running
> >a load, SQLLOADER seems to only want to handle 255 characters based
 on
> >it's use of the CHAR datatype as a defult for delimited file text
> >fields. Ok. So, I add VARCHAR(2000) in the .ctl file next to the
 fields
> >that I want to take larger datasets. That does not seem to work.
> >
> >When I set a field in the control file to VARCHAR(2000), the data for
> >that field will get into the table. That's fine but, the issue is
> >SQLLOADER does not just put just that field's data into the table,
 but
> >it puts the remainder of the record into the VARCHAR(2000) field.
> >SQLLOADER seems to fix the length of the field and forgets I want
> >delimiters to continue to work.
> >
> >Anyone know how to get SQLLOADER to handle multiple >255 data fields
 in
> >a delimited file load?
> >
> >jk
> >
> >
> >
> >Here is my control file:
> >
> >load data
> >infile 'BOOK2.csv'
> >append into table PARTNER_CONTENT_TEMP
> >fields terminated by ',' optionally enclosed by '^' TRAILING NULLCOLS
> >(ctlo_id,
> > partners_id,
> > content2_byline ,
> > content2 varchar(4000),
> > content3 varchar(2000),
> > content9 varchar(1000),
> > submitted_by,
> > pstr_id,
> > csub_id)
> >
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Sep 29 2000 - 16:17:16 CDT