Re: SQLLOADER: 255 CHAR limit

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.
Received on Thu Sep 21 2000 - 00:00:00 CDT