Re: SQL*Loader (7.3.4.2) and multi-line record

It's a little tricky to get this right, so what does your control file look like?

Also are your records fixed length?

You said:

"However, SQL*Loader, despite being told in its control file to delimit columns by "|", gets to "be," and calls it quits."

Are you really working with a delimited (variable-length) file *and* the records are split among multiple lines? I don't know how to do that kind of situation, but if you are working with a fixed-length file and using a "continuation" or "concatenation" character (for lack of a better word), here's how to do it.

BTW - IMHO, this doesn't work as "intuitive" as one would think, either.

Here's an example of two logical records made up of three physical records and the pipe sign used as the concatenation character:

This is my text of line 1, record 1 ....yadda yadda yadda|
This is my text of line 2, record 1 ....yadda yadda yadda|
This is my text of line 3, record 1 ....yadda yadda yadda
This is my text of line 1, record 2 ....yadda yadda yadda|
This is my text of line 2, record 2 ....yadda yadda yadda|
This is my text of line 3, record 2 ....yadda yadda yadda

Assume that the last "yadda" of every line ends in position 131 and the pipe sign that is the flag to concatenate lines is in position 132 (where applicable).

To load this, my control file would have this:

CONTINUEIF THIS (132) = '|' Next (this is the part that's not intuitive, IMHO, but it's how it works), SQL*Loader will find that character in position 132 and look at the next line as part of the same record, BUT the first character of the next line also is position 132 (not 133) for loading purposes, especially important in a fixed-length file. So, the 'T' in 'T'his is position 132 of the *record*, yet position 1 of the file. So to load the second line as part of the first, your file should be like this:

column_name position(132:<some number>)

(My bone of contention here is that I wanted to call the position that line two started in as 133, not 132, but it's 132 as far as SQL*Loader is concerned.)

Now we get to the end of that line, another pipe sign is in position 132 of the file and the 'T'his of the third line of record 1 starts in position 263 (for SQL*Loader purposes) of the record even though it's in position 1 of the file. and your file should be like this:

column_name position(263:<some number>)

Good luck...I pulled my hair out off and on for something like two weeks doing this nonsense until I figured out that the obvious wasn't the right answer. Nearly threw my PC out the window, too!

HTH,
--
Matt Brennan
SQL*Tools Specialist
GERS Retail Systems
9725-C Scranton Road
San Diego, California 92121
1-800-854-2263
mbrennan_at_gers.com
(Original email address is spam-blocked.)

Dave Brady <brady_at_fas.harvard.edu> wrote in article <6nts9l$ev9$1_at_news.fas.harvard.edu>...
> I'm trying to load, into a LONG datatype, a field which consists of
several
> physical lines from a file, such as:
>
> This will be
> one field
> in the table|
>
> However, SQL*Loader, despite being told in its control file to delimit
> columns by "|", gets to "be," and calls it quits.
>
> Any ideas (I hope)?
>
> --
>
> Dave Brady FAS Computer Services
> Sr UNIX Sys Admin/Programmer Harvard University
> brady_at_fas.harvard.edu LL1A Science Center
> Phone: (617) 495-1273 1 Oxford Street
> FAX: (617) 495-1210 Cambridge, MA 02138
Received on Tue Jul 07 1998 - 15:34:19 CDT