set markup csv

One thing always found a pain was the way Oracle would output to CSV.

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