Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How would you write a control file for Oracle SQL Loader that treats each record as being composed of 3 commas?

I have read the documentation, however I cannot find how to handle this scenario, without having to edit my data file by hand.

For example, my data file has:


I would like to load the data into the table looking like this:

col1       col2       col3

fd         3232       gfd67gf
peas       989767     jkdfnfgjhf
dhdhjsk    267        ujfdsy
fuyds      637296     ldosi
fduy       873        fuisouyd
try\nsave  2837       ipoi

Note: Where I have "\n" in the last row of col1, I mean an actual line feed not literally "\n".

share|improve this question
Is there a reason why you can't modify the input file? It's trivial to script a modification. –  FreshPhilOfSO Apr 14 '12 at 12:06
I thought SQLLoader could handle this sort of thing –  kloppers Apr 14 '12 at 12:08
Does this help:? How does one load records with multi-line fields? –  ypercube Apr 14 '12 at 13:11
The \n is doable with the link provided by @ypercube above. However, I can't find any way of dealing with multiple records on the same row in this situation. I could be wrong, but I've just spent 1/2 hour trying to massage them in & got nowhere. –  FreshPhilOfSO Apr 14 '12 at 16:49
Even with ypercube's link it shows no way of saying a record is formed of 3 commas –  kloppers Apr 15 '12 at 0:59
add comment

1 Answer

So the linefeed before peas and dhdhjsk should be skipped but the linefeed before save is part of the data. Which linefeeds should be skipped and which are part of the data? I think such a format is very error prone and should be avoided. Perhaps you can do it the following way but I did not verify this

  • define "," as the physical record separator
  • join three physical records to on logical records
  • to skip a leading \n trim leading whitespace from fields or use sql-functions

The following links to the manual can provide information:

Concatenating physical records to logical records

Input formats


Apply SQL functions

Specifying delimiters

share|improve this answer
I'd say it's not a valid answer if you haven't verified it. –  FreshPhilOfSO Apr 16 '12 at 10:17
@Phil Answers do not have to be verified to be accurate. I think people asking questions would prefer an unverified answer over no answer at all. Even if an answer turns out to be wrong it will at least provide a guidepost to what doesn't work. Disclosing that the answer has not been verified is beneficial and allows the OP and voting system to verify the answer. I haven't verified the answer either, but I voted for it because I believe it is headed in the right direction. –  Leigh Riffel Apr 16 '12 at 13:20
Ok, no worries. I was just unsure, as it looks to be troublesome. –  FreshPhilOfSO Apr 16 '12 at 13:55
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.