| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQLLOADER: 255 CHAR limit
Actually we have.
Example:
OPTIONS (SILENT=(FEEDBACK) BINDSIZE=4194304)
LOAD DATA
INFILE '/project/edr/data/05_masterchem.txt' BADFILE
'/project/edr/data/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 '"'
This control file loads rows that have multiple 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:48:14 CDT
 |
 |