How do I spool to a CSV formatted file using SQLPLUS?

You could also use the following, although it does introduce spaces between fields.

set colsep ,     -- separate columns with a comma
set pagesize 0   -- No header rows
set trimspool on -- remove trailing blanks
set headsep off  -- this may or may not be useful...depends on your headings.
set linesize X   -- X should be the sum of the column widths
set numw X       -- X should be the length you want for numbers (avoid scientific notation on IDs)

spool myfile.csv

select table_name, tablespace_name 
  from all_tables
 where owner = 'SYS'
   and tablespace_name is not null;

Output will be like:

    TABLE_PRIVILEGE_MAP           ,SYSTEM                        
    SYSTEM_PRIVILEGE_MAP          ,SYSTEM                        
    STMT_AUDIT_OPTION_MAP         ,SYSTEM                        
    DUAL                          ,SYSTEM 
...

This would be a lot less tedious than typing out all of the fields and concatenating them with the commas. You could follow up with a simple sed script to remove whitespace that appears before a comma, if you wanted.

Something like this might work...(my sed skills are very rusty, so this will likely need work)

sed 's/\s+,/,/' myfile.csv 

It's crude, but:

set pagesize 0 linesize 500 trimspool on feedback off echo off

select '"' || empno || '","' || ename || '","' || deptno || '"' as text
from emp

spool emp.csv
/
spool off

You can explicitly format the query to produce a delimited string with something along the lines of:

select '"'||foo||'","'||bar||'"'
  from tab

And set up the output options as appropriate. As an option, the COLSEP variable on SQLPlus will let you produce delimited files without having to explicitly generate a string with the fields concatenated together. However, you will have to put quotes around strings on any columns that might contain embedded comma characters.

prefer to use "set colsep" in sqlplus prompt instead of editing col name one by one. Use sed to edit the output file.

set colsep '","'     -- separate columns with a comma
sed 's/^/"/;s/$/"/;s/\s *"/"/g;s/"\s */"/g' $outfile > $outfile.csv

There is a problem using sqlplus to create csv files. If you want the column headers only once in the output and there are thousands or millions of rows, you cannot set pagesize large enough not to get a repeat. The solution is to start with pagesize = 50 and parse out the headers, then issue the select again with pagesize = 0 to get the data. See bash script below:

#!/bin/bash
FOLDER="csvdata_mydb"
CONN="192.168.100.11:1521/mydb0023.world"
CNT=0376
ORD="0376"
TABLE="MY_ATTACHMENTS"

sqlplus -L logn/pswd@//${CONN}<<EOF >/dev/null
set pagesize 50;
set verify off;
set feedback off;
set long 99999;
set linesize 32767;
set trimspool on;
col object_ddl format A32000;
set colsep ,;
set underline off;
set headsep off;
spool ${ORD}${TABLE}.tmp;
select * from tblspc.${TABLE} where rownum < 2;
EOF
LINES=`wc -l ${ORD}${TABLE}.tmp | cut -f1 -d" "`
[ ${LINES} -le 3 ] && {
  echo "No Data Found in ${TABLE}."
}
[ ${LINES} -gt 3 ] && {
  cat ${ORD}${TABLE}.tmp | sed -e 's/ * / /g' -e 's/^ //' -e 's/ ,/,/g' -e 's/, /,/g' | tail -n +3 | head -n 1 > ./${ORD}${TABLE}.headers
}

sqlplus -L logn/pswd@//${CONN}<<EOF >/dev/null
set pagesize 0;
set verify off;
set feedback off;
set long 99999;
set linesize 32767;
set trimspool on;
col object_ddl format A32000;
set colsep ,;
set underline off;
set headsep off;
spool ${ORD}${TABLE}.tmp;
select * from tblspc.${TABLE};
EOF
LINES=`wc -l ${ORD}${TABLE}.tmp | cut -f1 -d" "`
[ ${LINES} -le 3 ] && {
  echo "No Data Found in ${TABLE}."
}
[ ${LINES} -gt 3 ] && {
  cat ${ORD}${TABLE}.headers > ${FOLDER}/${ORD}${TABLE}.csv
  cat ${ORD}${TABLE}.tmp | sed -e 's/ * / /g' -e 's/^ //' -e 's/ ,/,/g' -e 's/, /,/g' | tail -n +2 | head -n -1 >> ${FOLDER}/${ORD}${TABLE}.csv
}

I wrote this purely SQLPlus script to dump tables to CSV in 1994.

As noted in the script comments, someone at Oracle put my script in an Oracle Support note, but without attribution.

https://github.com/jkstill/oracle-script-lib/blob/master/sql/dump.sql

The script also also builds a control file and a parameter file for SQL*LOADER

spool D:\test.txt

    select * from emp
    
    spool off