title: Using carriage return line feed in SQL Loader Command Line
body: <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>