Using carriage return line feed in SQL Loader Command Line
<p>I am trying to use SQL Loader in a shell script so I am using command line parameters. I create the control file on the fly and later delete it inside the script after loading. My problem is that my data file has carriage returns and a lot of the records are discarded to .bad. I understand I would need to use "str '\n'" after the INFILE is specified. But how do I do that with command line?</p>

<p>Below is a sample of my code. Any help is greatly appreciated.</p>

<pre><code>   LDRFILE=$LOCAL_HOME/data/rmse_loc.ctl
   echo "LOAD DATA " &gt; $LDRFILE
   echo "TRUNCATE "    &gt;&gt; $LDRFILE
   echo "INTO TABLE "R2SCHD.RDF_LOC_HIER"" &gt;&gt; $LDRFILE
   echo "FIELDS TERMINATED BY ',' " &gt;&gt; $LDRFILE
   echo "OPTIONALLY ENCLOSED BY '\"' " &gt;&gt; $LDRFILE
   echo "("  &gt;&gt; $LDRFILE
   echo "LOC, " &gt;&gt; $LDRFILE
   echo "LOC_NAME, " &gt;&gt; $LDRFILE
   echo "DISTRICT, "  &gt;&gt; $LDRFILE
   echo "DISTRICT_NAME, " &gt;&gt; $LDRFILE
   echo "DEFAULT_WH, " &gt;&gt; $LDRFILE
   echo "DEFUALT_WH_NAME, " &gt;&gt; $LDRFILE
   echo "LOC_TYPE, " &gt;&gt; $LDRFILE
   echo "LOC_TYPE_NAME " &gt;&gt; $LDRFILE
   echo ")"  &gt;&gt; $LDRFILE
   sqlldr userid=$CONNECT control=$LDRFILE log=$LOCAL_HOME/log/$PROGRAM_NAME.$INPUT_FILE.$RUN_DATE.log bad=$LOCAL_HOME/error/$PROGRAM_NAME.$INPUT_FILE.$RUN_DATE.bad data=$LOCAL_HOME/data/$INPUT_FILE
</code></pre>

<p>Below is a sample of the first 5 rows of the data file all line ends in CR/LF:</p>

<pre><code>34,"34 CARLISLE 67TH ST",3740,"CARLISLE NORTH",6620363,"DDDC REPLENISHMENT",S,"Store"^M
240,"240 FELTWELL SHOP",7614,"UK EAST",2220671,"GDC REPLENISHMENT",S,"Store"^M
5504,"5504 AM CLASS SIX",3720,"SAM S TEXAS",6620363,"DDDC REPLENISHMENT",S,"Store"^M
5827,"5827 JAC CLASS 6",632,"JACKSON/SHAW",9820580,"DDDC REPLENISHMENT",S,"Store"^M
100081,"100081 EGLIN MCS",944,"EGLIN AFB",6620363,"DDDC REPLENISHMENT",S,"Store"^M
</code></pre>