Re: Is there a way to specify a record delimitor for SQL*Loader rather than accepting the default LF CR?

Mark -

See below...

Mark wrote:

> Thank you for the tips.
>
> However, I have a couple of more questions:
>
> 1. The str substitution techniques works for 8i and above, but not for 7.X,
> correct?
>

Sorry. I don't have an opinion on this. I don't have easy access to the version 7 manuals, but I'd be real surprised if the "str" clause was new in Oracle8i. I'm currently looking in the section entitled "Assembling Logical Records from Physical Records" in chapter five of "Oracle8i Utilities Release 2 (8.1.6)", Oracle part number A76955-01, for reference to the "str" clause. See if your copy of the version 7 materials has a similar section.

>
> 2. In the example provided where the str option employs a hex conversion,
> how am I to interpret the string statement?
>
> INFILE 'g:\8pafir8x.txt' "str X'0d0a'"
>
> Does SQL*Loader know to replace the default record delimiter with what is in
> the string, which in this case is already 0d0a in hex, or linefeed, carriage
> return. Why replace the default delimiter with itself? How does SQL*Loader
> know how to substitute the record separator with the one of your choice, say
> a grave (`)?
>

Yeah, I think that you're absolutely right about replacing a delimiter with itself. I just offered this line as an example of how to set the delimiter

with something else. I believe that I originally used this separator when I used SQL*Loader on my PC with a datafile of unknown origin. (That datafile exhibited the telltale "^M" at the end of a line when I used
vim to edit it on my PC, for what it's worth.) I get headaches thinking about the various delimiters,
so please don't make me explain all this because I can't. I messed around with the "str" clause in my control file until I got the darn thing to load the way I

wanted it to.

If an input datafile was created on a given OS, and then SQL*Loader run on that same OS against the datafile, then a "str" clause is not necessary. However,
if the creation and the execution occur on different OSes, then the "str" is necessary, I believe. That's my story, and I'm stickin' to it.

Do your own experiments with your control files until your stuff loads correctly.
That'll provide the ultimate answer, won't it? ;-)

I'm confused about the last part of your post. SQL*Loader isn't editing anything,
but rather just using the "str" clause to know when to determine the end of a record
and then do some loading. If you want to use a grave as a record delimiter, knock
yourself out. You know how to use "str" now. If you want to use a grave as a column separator, use the "terminated by" clause.

Am I being unnecessarily difficult?

Bye,
TG

> Thank you,
>
> Mark
>
> "Mark" <mzquincey_at_hotmail.com> wrote in message
> news:fwdG8.134$oD.4604979_at_newssvr17.news.prodigy.com...
> > Is there a way to specify a record delimiter for SQL*Loader rather than
> > accepting the default LF CR?
> >
> >
> >
Received on Tue May 21 2002 - 18:28:36 CDT