SQL*Loader and carriage returns

From: Kyle G. Miller <kmiller_at_spencergrace.com>
Date: Fri, 17 Mar 2000 16:49:07 -0600
Message-ID: <38d2b663.0@csoux1102>

I am using Data Loader to input data into a table. I have a VARCHAR2(4000) field that contains text that includes carriage returns. When I try to run the load, it fails with the following message:
"Record 1: Rejected - Error on table BIG, column CATEGORY_ORDERING_RULES.
second enclosure string not present"

My control file and table defn is at the end of this message.

How can I use data loader to load data that has carriage returns in some of the fields?

I got a reply from the "ask an expert" section on oracle's site, but his/her reply (see bottom of this message) didn't work when I tried it, even with the exact code they used to test. I posted another request to ask an expert, but it usually takes 3 days for an answer and I need this Monday AM. I see what he is trying to do with the "str '|'" appended to the INFILE, but when I run the load, it just hangs, and the SQLLOAD80 process hangs my NT system at 100% utilization.

Please help.

Thanks.

Kyle Miller

Here is my control file:
LOAD DATA
INFILE 'c:\hargrovedata\realdataWithCR.csv' INTO TABLE big
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' trailing nullcols
(show_name, show_location_name, show_address1,

show_address2, show_city, show_state, show_zipcode,
show_phone, show_fax, show_industry, show_start_date,
show_end_date, registration_deadline_date, show_producer,
vendor_name, vendor_small_image, vendor_long_description,
vendor_address1, vendor_address2, vendor_city,
vendor_state, vendor_zipcode, vendor_phone, vendor_phone2,
vendor_fax, vendor_email_address, vendor_contact_name,
vendor_acceptable_payment1, vendor_acceptable_payment2,
vendor_acceptable_payment3, vendor_acceptable_payment4,

vendor_acceptable_payment5, vendor_acceptable_payment6, vendor_discount_date, category_name,
category_ordering_rules CHAR(4000), ...many more fields)

The table defn is as follows:
CREATE TABLE big
(

show_name   varchar2(60),
show_location_name  varchar2(255),
show_address1   varchar2(30),
show_address2   varchar2(30),
show_city   varchar2(30),
show_state   varchar2(10),
show_zipcode   varchar2(10),
show_phone   varchar2(16),
show_fax   varchar2(16),
show_industry   varchar2(35),
show_start_date   DATE,
show_end_date   DATE,

registration_deadline_date DATE,
show_producer varchar2(25),

vendor_name   varchar2(50),
vendor_small_image  varchar2(25),
vendor_long_description  varchar2(255),
vendor_address1   varchar2(40),
vendor_address2   varchar2(40),
vendor_city   varchar2(40),
vendor_state   varchar2(10),
vendor_zipcode   varchar2(10),
vendor_phone   varchar2(16),
vendor_phone2   varchar2(16),
vendor_fax   varchar2(16),
vendor_email_address  varchar2(50),
vendor_contact_name  varchar2(50),
vendor_acceptable_payment1 varchar2(20),
vendor_acceptable_payment2 varchar2(20),
vendor_acceptable_payment3 varchar2(20),
vendor_acceptable_payment4 varchar2(20),
vendor_acceptable_payment5 varchar2(20),
vendor_acceptable_payment6 varchar2(20),
vendor_discount_date  DATE,

category_name varchar2(255),
category_ordering_rules varchar2(4000), ...many more fields)
;

Here is one of the data records:

TS2000,Washington Convention Center,"900 9th Street, NW",B Hall,Washington,DC,20001,000-000-0000,000-000-0000,Trade Show,26-Jul-00,28-Jul-00,,NTP,"Hargrove, Inc",,,Number One Hargrove
Drive,,Lanham,MD,20706,301-306-4627,,301-731-5438,customerse rvice_at_hargroveinc.com,,visa,mc,amex,,,,10-Jul-00,Material Handling,"Small package rates apply to shipments with a total weight of 30 pounds or less.
Material Handling is billed by the hundredweight, or per CWT, with a 2 CWT minimum.
When computing material handling costs, remember to round up to the next hundred pounds. For example, a delivery tht weighs 347 pounds will be billed at 4 CWT. Each delivery to the dock constitutes a shipment, and is billed accordingly.
Refer to the Freight Handling Hints provided in your manual for more information about Material Handling. Be sure to check the show information and show schedule carefully to determine whether or not overtime surcharges should be included in your material handling estimate.",...many more fields

  Reply (submitted on : 03/15/2000 11:39 PST )

Kyle,
What you can do is terminate each record with a special character "|" for example, and then specify that character in your control file. For example, I tested this with the following control file...

load data
infile 'cr_test.dat' "str '|'"
into table dept
append
fields terminated by ',' optionally enclosed by '"' ( deptno, dname, loc )

  Question Reopened (submitted on : 03/17/2000 13:19 PST )

when I add the "str'|'" to my control file, the data load hangs, and I get no output. Can you let me know why it just hangs now? Nothing changed except the addition of the
"str '|'" to the infile line of control file.
Received on Fri Mar 17 2000 - 16:49:07 CST