title: Header formatting while spooling a csv file in sqlplus
body: <p>I am required to spool a csv from a table in Oracle, using sqlplus. Following is the format required:</p>
  
  <pre><code>"HOST_SITE_TX_ID","SITE_ID","SITETX_TX_ID","SITETX_HELP_ID"
  "664436565","16","2195301","0"
  "664700792","52","1099970","0"
  </code></pre>
  
  <p>Following is the relevant piece of the shell script I wrote:</p>
  
  <pre><code>sqlplus -s $sql_user/$sql_password@$sid &lt;&lt; eof &gt;&gt; /dev/null
      set feedback off
      set term off
      set linesize 1500
      set pagesize 11000
    --set colsep ,
    --set colsep '","'
      set trimspool on
      set underline off
      set heading on
    --set headsep $
      set newpage none
  
  
      spool "$folder$filename$ext"
      select '"'||PCL_CARRIER_NAME||'","'||SITETX_EQUIP_ID||'","'||SITETX_SITE_STAT||'","'||SITETX_CREATE_DATE||'","'||ADVTX_VEH_WT||'"'
      from cvo_admin.MISSING_HOST_SITE_TX_IDS;
      spool off
  </code></pre>
  
  <p>(I have used some commented statements in, to signify the things that I tried but couldn't get to work)</p>
  
  <p>The output I receive is:</p>
  
  <pre><code>'"'||PCL_CARRIER_NAME||'","'||SITETX_EQUIP_ID||'","'||SITETX_SITE_STAT||'","'||SITETX_CREATE_DATE||'","'||ADVTX_VEH_WT||'"'
  "TRANSPORT INC","113","00000000","25-JAN-13 10.17.51 AM",""
  "TRANSPORT INC","1905","00000000","25-JAN-13 05.06.44 PM","0"
  </code></pre>
  
  <p>Which shows that the header is messed up - it is literally printing the whole string that should have been interpreted as an sql statement, as is the case with the data displayed. </p>
  
  <p>Options I am considering: </p>
  
  <p>1) Using colsep</p>
  
  <pre><code>set colsep '","'
  spool
  select * from TABLE
  spool off
  </code></pre>
  
  <p>This introduces other problems as the data having leading and trailing spaces, first and the last values in the files are not enclosed by quotes</p>
  
  <pre><code>    HOST_SITE_TX_ID","   SITE_ID"
      "             12345","      16"
      "             12345","      21
  </code></pre>
  
  <p>I concluded that this method gives me more heartburn than the one I described earlier.</p>
  
  <p>2) Getting the file and use a regex to modify the header.</p>
  
  <p>3) Leaving the header altogether and manually adding a header string at the beginning of the file, using a script</p>
  
  <p>Option 2 is more doable, but I was still interested in asking, if there might be a better way to format the header somehow, so it comes in a regular csv, (comma delimited, double quote bounded) format. </p>
  
  <p>I am looking to do as less hard coding as possible - the table I am exporting has around 40 columns and I am currently running the script for around 4 million records - breaking them in a batch of around 10K each. I would really appreciate any suggestions, even totally different from my approach - I am a programmer in learning.</p>