asked on

New line character needed while loading data using Sql Loader

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.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Devinder Singh Virdi

ASKER

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?

slightwv (䄆 Netminder)

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.

Devinder Singh Virdi

ASKER

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.

slightwv (䄆 Netminder)

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 Virdi

ASKER

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.