How do I use SQL Loader to load data containing line breaks to a table?

Question

I have a data file, a csv. One record from the data file looks like this:

195268,"Organization",Active,,"This is a long description. This is a long description. This is a long description. This is a long description. This is a long description. This is a long description. This is a long description. This is a long description. This is a long description.</p>
<p>&nbsp;</p>
<p><a href=""https://app.place.com/news/"">Another Sample Link</a></p>
<p><a title=""Sample Link"" href=""https://app.place.com/202290"">Sample Link</a></p>","123 Fake St.","123 Fake St.",City,99999,"(555) 555-5555",,,email@email.com,http://app.place.com,https://www.app.com/org/,,https://www.flickr.com/photos/testOrganization/,,,https://images.com/test.jpg,https://app.place.com/app/organization/Organizations,Visible,Closed,18664,Branch,,,14097304,"Org Front Desk",email@email.com

I need to load it to a table with the following schema:

CREATE TABLE ORGS
 ( 
     organizationId varchar2(6),
     name varchar2(150),
     status varchar2(8),
     shortName varchar2(100),
     summary varchar2(500),
     description CLOB,
     addressStreet1 varchar2(200),
     addressStreet2 varchar2(200),
     addressCity varchar2(100),
     addressZipPostal varchar2(10),
     phoneNumber varchar2(20),
     extension varchar2(20),
     faxNumber varchar2(20),
     email varchar2(100),
     externalWebsite varchar2(250),
     facebookUrl varchar2(250),
     twitterUrl varchar2(250),
     flickrFeedUrl varchar2(250),
     youtubeChannelUrl varchar2(250),
     googleCalendarUrl varchar2(250),
     profileImageUrl varchar2(250),
     profileUrl varchar2(250),
     directoryVisibility varchar2(50),
     membershipType varchar2(10),
     typeId varchar2(5),
     typeName varchar2(100),
     parentId varchar2(6),
     parentName varchar2(50),
     primaryContactId varchar2(8),
     primaryContactName varchar2(100),
     primaryContactCampusEmail varchar2(100)
 );

How can I build an sqlLoader control file for this load?

I tried this ctl file

LOAD DATA
INFILE 'orgs.csv' "str '|\n'"
INTO TABLE ORGS
FIELDS TERMINATED BY ','
(
     organizationId,
     name,
     status,
     shortName,
     summary,
     description,
     addressStreet1,
     addressStreet2,
     addressCity,
     addressZipPostal,
     phoneNumber,
     extension,
     faxNumber,
     email,
     externalWebsite,
     facebookUrl,
     twitterUrl,
     flickrFeedUrl,
     youtubeChannelUrl,
     googleCalendarUrl,
     profileImageUrl,
     profileUrl,
     directoryVisibility,
     membershipType,
     typeId,
     typeName,
     parentId,
     parentName,
     primaryContactId,
     primaryContactName,
     primaryContactCampusEmail
)

And this one

LOAD DATA
INFILE 'orgs.csv' 
CONTINUEIF LAST != '"'
INTO TABLE ORGS
APPEND
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
     organizationId,
     name,
     status,
     shortName,
     summary,
     description,
     addressStreet1,
     addressStreet2,
     addressCity,
     addressZipPostal,
     phoneNumber,
     extension,
     faxNumber,
     email,
     externalWebsite,
     facebookUrl,
     twitterUrl,
     flickrFeedUrl,
     youtubeChannelUrl,
     googleCalendarUrl,
     profileImageUrl,
     profileUrl,
     directoryVisibility,
     membershipType,
     typeId,
     typeName,
     parentId,
     parentName,
     primaryContactId,
     primaryContactName,
     primaryContactCampusEmail
)

But oracle has been complaining that, for various fields,

Fields in the data file exceeds maximum length.

I'm positive that the data in the data file is sized correctly for the fields I have created. I'm wondering if I am defining the datatypes incorrectly in the ctl file or that there is an issue with the fields containing line breaks contained in quotes.

Answer 1

It's been a while since I've loaded fields containing newlines, so I don't have any comment on that part. But check out this answer - the default SQLLDR data length is 255 characters, so if one of your fields is longer than that, you have to specify it in the control file. For the summary field, I'd just try changing

summary,

to

summary char(500),

For the description field, it's a little more complicated. AFAIK, you have to set some kind of max length for delimited character fields - if you're not worried about it, you could set it to something large, like

description char(30000),

Oracle says that a value up to 2 gigabytes can be specified, so I guess you could try 2147483647 instead of 30000 if you really don't want to run into length limits.

You might also want to think about what you want the value of ORGS.DESCRIPTION to be if the CSV field is blank.

description char(30000) NULLIF description=BLANKS, (sets it to null)
description char(30000) DEFAULTIF description=BLANKS, (sets it to an empty LOB)