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

Posted by Jiltin     1 December, 2008    467 views   

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"
#
# FCP_LOGIN is standard in oracle applications, here I substituted

FCP_LOGIN="apps/pwd@database"

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

[Post to Twitter]  [Post to Delicious]  [Post to Digg]    [Post to StumbleUpon] 

Categories : 11i Scripts, Scripts Oracle, Scripts Unix, Web & Scripts Tags : , , , , , ,

Comments

No comments yet.


Leave a comment

(required)

(required)