| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: need help with sql*loader problem
Friday, May 21, 2004, 4:57:25 PM, david wendelken (davewendelken_at_earthlink.net) wrote:
dw> Unfortunately, the access data has carriage returns in the long textual data fields, so dw> instead of one line per record, I get lots of lines per record. Sql*Loader is interpreting dw> each line as a new record.
You can change the record delimiter from a carriage return to something else. I cover that in my book, though at the moment I forget the exact keyword to use, and where to use it. I believe I cover it way in the back of the book, in a chapter on loading CLOB data, or maybe nested table data. If you do change the record delimiter, then all those carriage returns become part of your logical record. That shouldn't be a problem if your fields are quoted and the carriage returns are outside the quotes. But if a carriage return occurs in the middle of a field, well, then you'll have a carriage return in the middle of your value. I suppose then, that you could use SUBSTR to eliminate it.
Oh, hey, I just found it on Safari. It's in chapter 11. You use STR, as follows, to change the stream delimiter:
INFILE 'waterfalls.dat' "str '***'"
And then here's some data to go with that:
Alger,"Tannery Falls","One of three nice falls in a nature
sanctuary located in Munising."
***
Alger,"Scott Falls","Scott Falls is a small, but very picturesque
waterfall that's easily seen from the road. It's easily seen
that is, as you travel east on M-28 out of Au Train. While
only 20-30 feet from the road, the falls face west. If you're
traveling west, you'll miss them unless you happen to look
in your rearview mirror at just the right moment. What you
won't miss is the Scott Falls Roadside Park--a scenic picnic
area on the shore of Lake Superior just across the road from
the falls."
***
Alger,"Chapel Falls","The highest waterfall in the Pictured
Rocks National Lakeshore. Chapel falls is where Section Creek
falls over a canyon wall towards Chapel Lake."
***
Does this help? If not, then you probably need a solution outside SQL*Loader.
Best regards,
Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://five.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri May 21 2004 - 19:33:31 CDT
 |
 |