Re: need help with sql*loader problem

Jonathan Gennick <jonathan@xxxxxxxxxxx> · Fri, 21 May 2004 20:35:21 -0400

Friday, May 21, 2004, 4:57:25 PM, david wendelken (davewendelken@xxxxxxxxxxxxx) 
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@xxxxxxxxxxx

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@xxxxxxxxxxx and 
include the word "subscribe" in either the subject or body.

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: