Tony
I haven't actually worked out the solution (too idle), but if you end
up on Unix you could use awk or similar to do the editing for you. You
can also do this as part of a processing pipeline (so the edited load
file never has to hit the disc).
If you haven't come across use of fifos / named pipes with sqlldr, the
basics are described here:
http://dylanwan.wordpress.com/2008/06/24/use-sqlloader-with-named-pipe/
(and in a zillion other places - this trick is decades old). Some
unixes use* mknod -p* rather than *mkfifo*. One particularly useful
application of the pipe is to allow you to load from a compressed file
without materializing the uncompressed file - and then you just add
your reformat command to the pipeline:
mknod -p mypipe.dat
gunzip -c infile.gz | awk -f awkfile.awk > mypipe.dat &
sqlldr u/p control=x.ctl data=mypipe.dat &
Cheers
On 21 March 2010 09:14, Tony Sequeira <tony@xxxxxxxxxxxxxxx
<mailto:tony@xxxxxxxxxxxxxxx>> wrote:
Hi Adar,
Andrey was indeed on the right lines. However, if you look back
at the postings I mention:
"I believe that I will have to manipulate the datafile..."
"without manipulating the datafile."
And indeed Adar solution worked, *BUT* I had to edit the relevant
datafiles, I've been given them, I have no opportunity to amend
the way they are created.
Regards.
Yechiel Adar wrote:
Read Andrey lips. The link he gave you is right on the money.
You are looking on the data with the wrong tool.
Since you produce somehow a csv file, you should be able to "select
*,'|' from table;".
The spooled output file is a long string of characters that contained /n
in some places.
Sqlldr reads and understand this string according to the parameters.
If you use the "str '|\n'" then sqlldr will treat /n as regular
character and use only |/n as end of record.
When you look at the file with a regular editor, the editor treats the
/n as new line and present it to you in one way.
Sqlldr will treat it in a different way.
Adar Yechiel
Rechovot, Israel
andrey khudyakov wrote:
http://www.orafaq.com/wiki/SQL*Loader_FAQ#How_does_one_load_records_with_multi-line_fields.3F
--
//www.freelists.org/webpage/oracle-l
--
S. Anthony Sequeira
++
Now I lay me down to study,
I pray the Lord I won't go nutty.
And if I fail to learn this junk,
I pray the Lord that I won't flunk.
But if I do, don't pity me at all,
Just lay my bones in the study hall.
Tell my teacher I've done my best,
Then pile my books upon my chest.
++
