Header formatting while spooling a csv file in sqlplus

Question

I am required to spool a csv from a table in Oracle, using sqlplus. Following is the format required:

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

Following is the relevant piece of the shell script 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 have used some commented statements in, to signify the things that I tried but couldn't get to work)

The output I receive is:

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

Options I am considering:

1) Using colsep

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

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

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

I concluded that this method gives me more heartburn than the one I described earlier.

2) Getting the file and use a regex to modify the header.

3) Leaving the header altogether and manually adding a header string at the beginning of the file, using a script

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.

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.

Solution 1

One easy way to have a csv with just one header 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

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

Solution 2

This is how I created a 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;

Solution 3

This is how you add a pipe delimited header to SQL statements. Once you spool it out that "something" wont be there

-- this creates the header
select 'header_column1|header_column2|header_column3' as something
From dual
Union all
-- this is where you run the actual sql statement with pipes in it
select 
rev.value1 ||'|'||
rev.value2 ||'|'|| 
'related_Rel' as something
from
...

Comments

[Andrew Barber]

I am having a difficult time seeing how this is an answer to the question asked here.

[Sam B]

how is this an answer??? he clearly said and I confirm adding colsep doesn't work all the time