Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: SQLLOADER: 255 CHAR limit

Re: SQLLOADER: 255 CHAR limit

From: Erik Cohen <cohene_at_sdc-moses.com>
Date: Fri, 27 Oct 2000 15:59:53 GMT
Message-ID: <39f9a290.12259881@news.bellatlantic.net>

Actually, I have. We have many columns that are like this. Example:  OPTIONS (SILENT=(FEEDBACK) BINDSIZE=4194304)  LOAD DATA
  INFILE '//data/05_masterchem.txt' BADFILE '/project//05_masterchem.bad'
  TRUNCATE
  INTO TABLE FD_MASTER_CHEMICAL_LIST
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'   TRAILING NULLCOLS
    (

 FD_SEQNO
,FD_CASRN
,FD_SYSTEMATIC_NAME                        CHAR(2000) TERMINATED BY
',' OPTIONALLY ENCLOSED BY '"'
,FD_SYSTEMATIC_NAME_CONTEXT
,FD_CHEMICAL_TYPE_CODE
,FD_MOLECULAR_FORMULA
,FD_MOLECULAR_WEIGHT
,FD_DEFINITION_TXT                         CHAR(2000) TERMINATED BY
',' OPTIONALLY ENCLOSED BY '"'
,FD_COMMENT_TXT                            CHAR(2000) TERMINATED BY
',' OPTIONALLY ENCLOSED BY '"'
,FD_SOURCE_ACRONYM
)

This example successfully loads rows with 3 oversized columns.

Erik.

On Fri, 29 Sep 2000 21:17:16 GMT, jkokinda_at_raymondkarsan.com wrote:

>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 Oct 27 2000 - 10:59:53 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US