Oracle dump to csv and keep NULL as empty string

Question

I have the following sql file:

SET COLSEP ';'
SET ECHO OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SET PAGESIZE 32766 -- Maximum number we can set.
                   -- i.e there will be duplication of headers. 
SET LINESIZE 32766 -- Maximum number we can set.SET NEWPAGE NONE
SET VERIFY OFF
SET TERM OFF
--SET TRIMS ON
SET TRIMSPOOL ON
SET UNDERLINE OFF

SPOOL dump_audit_table.csv

SELECT timestamp, PROXY_SESSIONID,sessionid FROM dba_audit_trail where rownum < 10;

set trimspool off
SPOOL OFF
EXIT

Which exports the following CSV:

TIMESTAMP          ;PROXY_SESSIONID; SESSIONID
04-29-2014 21:46:40;               ; 707304320
04-29-2014 21:46:40;               ; 707304320
04-29-2014 21:46:46;               ; 707452228
04-29-2014 21:46:46;               ; 707452228
04-29-2014 21:46:46;               ; 707452228
04-29-2014 21:46:46;               ; 707452228

My Problem is: PROXY_SESSIONID nulls are represented as ' ' a string with column width of spaces. I could not find a way to completely terminate this. I would like the file to be exported in the following way:

 04-29-2014 21:46:46;;707452228

e.g: NULL should be ;; read: empty string! SESSIONID should not have a leading space.

Answer 1

This is how I normally make .csv files. You should be able to open the file with Excel.

SET TERMOUT OFF
SET FEEDBACK OFF
SET ECHO OFF
SET PAGESIZE 0
SET LINESIZE 2048
COLUMN text format A2048
SET VERIFY OFF
SET TRIMSPOOL ON
SET TAB OFF
SET UNDERLINE OFF
SET TERMOUT ON

SPOOL dump_audit_table.csv

SELECT '"'||timestamp||'","'||PROXY_SESSIONID||'","'||sessionid||'"' text
FROM dba_audit_trail where rownum < 10;

SPOOL OFF
QUIT