How to extract,using bash/unix/linux shell scripting, the oracle data like excel report and send as email attachment?

Monday, December 1st, 2008 at 2:12 pm by Jiltin
Filed under: 11i Scripts, Oracle Scripts, Unix Scripts, Web & Scripts 

This script provides an sample, and then the practical example to connect to oracle database, extract data like excel sheet and attach it with an email list.

First connecting is done with “sqlplus -s $FCP_LOGIN” in silent mode.Then EOF marks the begining of sql statement and another EOF marks end of it.The chr(9) provides the tab required to make this as excel sheet.In fact, it creates a csv file with tab delimited entry.Since the filename ‘FILENAME=”Credit_Card_Failure_Report.xls”‘ ends with xls sheet, when the user opens the file, it automatically converts into excel sheet.The UUENCODE attaches the file as email attachment.

Practical Example1

Practical Example2

#!/bin/sh
#
# P_EMAIL_ADDRESS_TO is a parameter from the concurrent job
#
P_EMAIL_ADDRESS_TO="jay@jiltin.com"
FILENAME="user_report.xls"
sqlplus -s $FCP_LOGIN<<EOF
SET echo OFF
SET feedback OFF
SET heading off
SET linesize 221
SET pagesize 0
SET newpage 0
spool $FILENAME

SELECT RPAD (SUBSTR (username, 1, 15), 15, ‘ ‘)    ||chr(9)
       || RPAD (SUBSTR (emails, 1, 15), 15, ‘ ‘) ||chr(9)
  FROM all_users;

EOF

if [ -f $FILENAME ]
then
        uuencode $FILENAME $FILENAME | mail -s "$FILENAME status! Reported on `date` " "$P_EMAIL_ADDRESS_TO"
fi

Tags: , , , , , ,

Comments