New line character needed while loading data using Sql Loader

Devinder Singh Virdi
Devinder Singh Virdi used Ask the Experts™
on
Hi,
I hava a flat file in which one column is breaked into two lines. When I load this file into table, oracle eliminates new line character. I want to retain that character. Pls help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Devinder Singh VirdiLead Oracle DBA Team

Author

Commented:
This document tells about newline character 10 and 13 when transfering file from window to unix, but not specific to how to enter new line char from unix flat file to oracle..

Can you please give me example, how we can do this?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Windows uses a 2 character line feed.  CR/LF or chr(13)/chr(10) or 0x0D0A

Unix only uses 1 a LF/chr(10)/0X0A.  I would assume you could just get rid of the 0D in the example.

I ran the example as-is from that link on Windows and it ran fine.  I don't have access to UNIX to test it there.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Devinder Singh VirdiLead Oracle DBA Team

Author

Commented:
The only difference is I am not getting new line character when selecting the record. When I compair length size of column between two databases(generated flat file and where I am testing), it is showing one byte less.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
The example in the link did have one extra trick in it.  Maybe that's the necessary trick to make it work.

It added a pipe '|' to the end on the line.  That is the 7C in the line:

INFILE demo20.dat "str X'7C0D0A'"

Just for grins add a '|' to the end of the line in your test and change the INFILE line to:

INFILE demo20.dat "str X'7C0A'"
Devinder Singh VirdiLead Oracle DBA Team

Author

Commented:
I reliazed that the data was very old and no one has corrected since they restrict to allow chr 10 and 13 and when I said its not possible in sqlldr, developer starts working on correcting the data. Which is good. Instead of modifying the tool, lets correct data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial