sql output to a csv file using shell scipt

We can simply take extract of an oracle sql statement to a csv file through a shell script. This can be used when we need to configure a job using crontab or so.Once crontab is configure to run the job, it will run the shell script and the shell will in turn run the sql statement and the same will create a csv file. Let's have a look at it.

For this we need to create a shell script I am calling it as Transaction.sh

# Path to generate the csv file
csv_path=HOME/Monthly_Reconciliation


echo &date
# File name
FILE="$csv_path/Customer_Settle_`date +%Y%m%d`.csv"

export ORACLE_HOME=/opt/app/oracle/product/11.2/client_1
export ORACLE_SID=atlas_etl_bistg_srv(my sid , please use yours)
sqlplus_folder="$ORACLE_HOME/bin"
$sqlplus_folder/sqlplus -s username/password@database(dv1dmh/testdmh etc..)<<EOF

SET PAGESIZE 0
SET NEWPAGE 0
SET COLSEP ','
SET LINESIZE 30000
SET FEEDBACK OFF
SET TRIMSPOOL ON
#SET TRIMOUT ON
SET ECHO OFF
SET VERIFY OFF
SET TERMOUT OFF
SET TAB OFF
SET SQLBLANKLINE ON
#SET TRIMS ON
# SPOOL is the command used to take the script and create the file
SPOOL $FILE
# Simple way to add header along with extract is to select those fields from DUAL. Tricky way.

select 'EMP_NAME','EMP_ID','AGE'
from dual
/
select emp_name,emp_id,age from employee
/
SPOOL OFF
SET FEEDBACK ON
SET VERIFY ON
SET TERMOUT ON
SET HEADING ON
SET TAB ON
SET SQLBLANKLINE OFF
SET TRIMSPOOL OFF
SET TRIMS OFF
SET ESCAPE OFF
EXIT
EOF

echo "Done"
echo &date
exit;

Make the above shell script in a document and name it as transaction.sh.
We can call it using ./transaction.sh after we step in the folder where we keep this file.