Formatting header when buffering csv file in sqlplus

Question

I need to spin csv from a table in Oracle using sqlplus. Below is the format:

"HOST_SITE_TX_ID","SITE_ID","SITETX_TX_ID","SITETX_HELP_ID" "664436565","16","2195301","0" "664700792","52","1099970","0" 

Below is the corresponding shell script fragment, I wrote:

 sqlplus -s $sql_user/ $sql_password@ $sid << eof >> /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 

(I used some of the statements made in the commentary to denote things that I tried but couldn't work)

The result obtained:

 '"'||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" 

Which shows that the header is confused - it literally prints the entire line, which should have been interpreted as an sql operator, as is the case with the displayed data.

The options I'm considering are:

1) Using colsep

 set colsep '","' spool select * from TABLE spool off 

This introduces other problems because data with leading and trailing spaces, the first and last values ​​in the files are not enclosed in quotation marks

  HOST_SITE_TX_ID"," SITE_ID" " 12345"," 16" " 12345"," 21 

I came to the conclusion that this method gives me more heartburn than the one I described earlier.

2) Getting the file and using a regular expression to change the header.

3) Leaving the header altogether and manually adding the title bar at the beginning of the file using the script

Option 2 is more doable, but I was still interested in asking if there could be a better way to format the header in some way, so it comes in the usual csv format (comma-delimited, double-quoted).

I try to keep hard coding as small as possible - the table I export has about 40 columns, and I'm currently running a script for about 4 million records - breaking them in batches of 10K each. I would really appreciate any suggestions, even completely different from my approach - I am a programmer in training.

Answer 1

One easy way to have a single header csv is to do

 set embedded on set pagesize 0 set colsep '|' set echo off set feedback off set linesize 1000 set trimspool on set headsep off 

embedded is a hidden option, but it is important to have JUST one header

Answer 2

This is how I created the header:

 set heading off /* header */ SELECT '"'||PCL_CARRIER_NAME||'","'||SITETX_EQUIP_ID||'","'||SITETX_SITE_STAT||'","'||SITETX_CREATE_DATE||'","'||ADVTX_VEH_WT||'"' FROM ( SELECT 'PCL_CARRIER_NAME' AS PCL_CARRIER_NAME , 'SITETX_EQUIP_ID' AS SITETX_EQUIP_ID , 'SITETX_SITE_STAT' AS SITETX_SITE_STAT , 'SITETX_CREATE_DATE' AS SITETX_CREATE_DATE , 'ADVTX_VEH_WT' AS ADVTX_VEH_WT FROM DUAL ) UNION ALL SELECT '"'||PCL_CARRIER_NAME||'","'||SITETX_EQUIP_ID||'","'||SITETX_SITE_STAT||'","'||SITETX_CREATE_DATE||'","'||ADVTX_VEH_WT||'"' FROM ( /* first row */ SELECT to_char(123) AS PCL_CARRIER_NAME , to_char(sysdate, 'yyyy-mm-dd') AS SITETX_EQUIP_ID , 'value3' AS SITETX_SITE_STAT , 'value4' AS SITETX_CREATE_DATE , 'value5' AS ADVTX_VEH_WT FROM DUAL UNION ALL /* second row */ SELECT to_char(456) AS PCL_CARRIER_NAME , to_char(sysdate-1, 'yyyy-mm-dd') AS SITETX_EQUIP_ID , 'value3' AS SITETX_SITE_STAT , 'value4' AS SITETX_CREATE_DATE , 'value5' AS ADVTX_VEH_WT FROM DUAL ) MISSING_HOST_SITE_TX_IDS;