Email sql-plus output as csv from Linux

Shell Scripts

This script can be used for sending sql output via email in CSV format as attachment.

#!/bin/ksh
# ————————————————————————-
# Script Name :
# Author : Hans Schoonen
# Version : 1.0
# ————————————————————————-

SetEnv()
{
NLS_LANG=American_America.UTF8 ; export NLS_LANG
ORACLE_HOME=[YOUR ORACLE HOME DIR] ; export ORACLE_HOME
BinDir=$ORACLE_HOME/bin ; export BinDir
}

GetData()
{
rm /usr/test/test.csv
touch /usr/test/test.csv
$BinDir/sqlplus -s apps/$pswd <<EOF
spool /usr/test/test.csv;
set serveroutput on size 1000000;
set verify off;
set wrap on;
set colsep ,;
set pagesize 1000;
set trimspool on;
set headsep off;
set linesize 9999;
@/usr/test/test.sql;
spool off;
exit
EOF

}

MaillData()
{
cat /usr/test/test.csv | uuencode test.csv | mail -s “Report ICT-OKE Data” info@ict-oke.com
}

SetEnv

GetData

MailData

exit