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.

Kiki

Answer 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

Cryogen

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

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

Answer 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;

Loyalty

Answer 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
...

Key

Answer 4

In Oracle 19 you can use set markup csv on to ensure that csv outputs are created.

You can also set the delimiter and optional quote or even spool html if you prefer

You can read more here

set markup csv on
spool "$folder$filename$ext"
select q'|wow, I can't beleive he said "hello, how are you?", can you beleive it!|' as text 
  from dual;
spool off
quit;

Bareheaded