Re: sqlldr / new line?

"Tino Korth" <korthNOSPAM_at_mhr.de> wrote in message news:b34r63$q9v$1_at_ngspool-d02.news.aol.com...
> hello,
>
> i'm using sqlldr to put a data (text) file into an oracle db.
> sqlldr has got problems with "new lines" in text-fields.
> how does i have to quote "new line"'s?
>
> the "problem dataset":
>
> 6,"2002-08-15 10:46:41","Test","Line1\nLine2\nLine3",0
>
>
> thanks!
>
> greetz from rostock, germany!
>
> tino
>

"\n" works if used inside the control file, as in TERMINATED BY "\n", you cannot "encode" newlines as you did, unless you replace "\n" using a the REPLACE SQL function upon loading, see at the bottom of the message. If you are trying to load a CSV file containing newlines embedded in quoted strings, then you're in trouble.
As far as I know newlines are interpreted as a record terminator, unless you specify something else as a record terminator or using the VAR\FIX alternate approaches. This means that a plain CSV file containing newlines inside quoted strings cannot be read by SQL*Loader successfully, you must write the file using some special record terminator, ideally a string that you are 100% sure is not going to be present in the quoted string at any time, CHR(0) perhaps or some other odd character combination.

In your case, as you are apparently willing to encode newlines as \n, why not replacing each pair \n with the CHR(13) || CHR(10) while you load the data, using the REPLACE string function ?

The syntax would be like this:

   ...
   column_name CHAR ENCLOSED BY '"' "REPLACE(:column_name, '\\n', CHR(13) || CHR(10))" Caveat: this works if the column datatype is VARCHAR2, not CLOB.

Bye,
Flavio Received on Fri Feb 21 2003 - 11:41:12 CST