Since 12.2 they have "set markup csv on" which should have been available many releases ago.
Asked to run some output for a select and output to CSV - so much easier.
Changed this script as the time output was using ":" and windows uses colons for drive letters and gets most upset if you copy if from Linux to Windows. The time is now separated using "_"
Anyway, example of output using markup CSV and spooling to a file with database name and timestamp (works for multiple databases under same home).
#!/bin/sh
oracle_env()
{
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome; export ORACLE_HOME
PATH=$PATH:$ORACLE_HOME/bin; export PATH
}
oracle_env
array=(`\ps -ef | egrep "(ora)_pmon_" | awk '{print $NF}' | sed 's/.*pmon_//'`)
for i in "${array[@]}" ; do
ORACLE_SID=${i}; export ORACLE_SID
echo $ORACLE_SID
exit | sqlplus -silent / as sysdba << eof
set markup csv on
set termout off
column dt noprint new_val X
column db noprint new_val Y
select to_char(sysdate,'yyyymmdd_HH24_MI_SS') dt from dual;
select global_name db from global_name;
spool all_tabs.&Y.&X.csv
select * from global_name;
SELECT distinct owner, table_name, Num_Rows FROM all_tables ORDER BY 1;
eof
done
