Shell scripting unable to send the sql query data in table in body of email


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Shell scripting unable to send the sql query data in table in body of email
# 1  
Old 09-26-2014
Shell scripting unable to send the sql query data in table in body of email

I have written a shell script that calls below sql file. It is not sending the query data in table in the body of email.
Code:
spool table_update.html;
SELECT * FROM PROCESS_LOG_STATS where process = 'ActivateSubscription';
spool off;
exit;

Moderator's Comments:
Mod Comment Please use code tags next time for your code and data. Thanks

Last edited by vbe; 09-26-2014 at 05:54 AM..
# 2  
Old 09-26-2014
Hello and Welcome to The UNIX and Linux Forums!

Your Post was moved to shell scripting forum

Please read the our FAQ on how to post new threads in the forums.

As a registered user you can go to any forum where you have permission to post and create new threads, view threads and leave replies. You should always search the forums before posting a new thread (Google Site Search).

To create a new thread, click on a forum you would like to post in, for example, UNIX for Dummies Questions & Answers, and then click on the new thread icon Image

To reply you have a few options. You can click on the 'Post Reply' button Image and add a new post to the end of the thread. Alternatively, you can leave a quick reply in a quick editor box listed below the posts in the thread.

If you have further questions on forum operations, do not send email or private messages (PMs) to us. You are required to post these questions here:

Post Here to Contact Site Administrators and Moderators



Enjoy, Learn and Teach!

The UNIX and Linux Forums
# 3  
Old 09-26-2014
Welcome to the forum
Quote:
I have written a shell script that calls below sql file. It is not sending the query data in table in the body of email.
To be able to give you an answer, we would need to know the OS and shell you are using, and see your script and the error messages if any, the little SQL code extract you gave is clueless to us
# 4  
Old 09-26-2014
Thanks Vbe for quick reply

I am using korn shell on an AIX machine..
shell script as below:
Code:
. /ptma/ptma.profile;
echo $DB_PASS | sqlplus $DB_USER@$DB_SID @/ptma/ActivationCountCheck/ActivationCountCheck.sql;
mail -s "Activation Count of Last Day" someone@something.com < table_update.html
exit;

while the sql file has:
Code:
spool table_update.html;
SELECT appid, sum(resp) 
FROM PROCESS_LOG_STATS 
where process = 'ActivateSubscription' and ddate > sysdate-2 group by process, appid ORDER BY process, appid;
spool off;

The mail is triggrred but body of mail has data in text format but not in proper report/excel/html format.

Moderator's Comments:
Mod Comment Please use code tags next time for your code and data. Thanks

Last edited by vbe; 09-26-2014 at 09:07 AM..
# 5  
Old 09-26-2014
Looks like you will have to send your report as an attachment if you want to keep the HTML format...

This is how I used to transfer fancy stuff and binaries: (Because I remember at the time I had issues when sending .doc or .xls files...)
Code:
cp myheader > /tmp/mailtmpfile
uuencode /path/datafile datafile >> /tmp/mailtmpfile
cat /tmp/mailtmpfile | sendmail -t 
rm /tmp/mailttmpfile


Last edited by vbe; 09-26-2014 at 09:25 AM..
# 6  
Old 09-26-2014
Thank you

Thank you moderator! I am on verge to get this info.. if I succeed, I will paste it here... Smilie I think its doable but its not easy.. I googled a lot but could not find it anywhere..
# 7  
Old 09-26-2014
What does this give as output?
Code:
uuencode -m table_update.html table_update.html|  mail -s "Activation Count of Last Day" someone@something.com

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Shell Script to read hive table and send email

HI Team, I am working on reading hive table and send email in email body using shell script, can you please help on fixing the errors: I have 6 columns in my hive table and trying to send the email in the mail body. below script: #!/bin/bash hive -e 'SELECT count(*) from db.table' >... (4 Replies)
Discussion started by: Mi4304
4 Replies

2. UNIX for Beginners Questions & Answers

Not able to generate table in email body

Below is the code snippet and I'm not able to generate the table in email, output email has nothing if ; then echo "File $fName exists." awk -F "," ' BEGIN { print "MIME-Version: 1.0" print "Content-Type: text/html" print "Subject: Out OF Network Fee - Portfolio Level Stats" print... (1 Reply)
Discussion started by: vyomdev
1 Replies

3. Shell Programming and Scripting

Regarding guidance related to HTML table in email body

Hello All, I have a query here. I am sending an HTML table(which I am creating it by a call to REST API, in a LINUX box) and from there I have to send it into an email. So following are the poins on same: As data is not static so it is writing Dynamic data and creating HTML file. There is... (9 Replies)
Discussion started by: RavinderSingh13
9 Replies

4. Shell Programming and Scripting

HTML table in email body using C Shell

I am using Sun Solaris ver. 5.10 and trying to send an HTML table in email body using mail command in C shell script. I tried following commands:- #1 mail -m "MIME-Version: 1.0;Content-type:text/html;charset=UTF-8" receiver@mail.com < file.html #2 mail -m "Content-type: text/html;" -s "This... (4 Replies)
Discussion started by: jnrohit2k
4 Replies

5. Shell Programming and Scripting

SQL query output convert to HTML & send as email body

Hi , I have a sql query in the unix script ,whose output is shown below.I want to convert this output to HTML table format & send email from unix with this table as email body. p_id src_system amount 1 A 100 2 B 200 3 C ... (3 Replies)
Discussion started by: jagadeeshn04
3 Replies

6. Shell Programming and Scripting

How to get full sql table data using shell script

i have a Oracle table like col1 col2 ---- ----- a 1 b 2 c 3 when i write a script for it , it gives me all the data in one column. Please give me the solution which gives the exact result like we see in sql editors. for a in `echo " set feedback off; set pagesize 40;... (1 Reply)
Discussion started by: ss135r
1 Replies

7. Shell Programming and Scripting

We need a script that invokes the sql query every 14 days ans send email

HI, We need a script that invokes the sql query every 14 days ans send email (0 Replies)
Discussion started by: bujjisveeru
0 Replies

8. Shell Programming and Scripting

We need a script that invokes the sql query every 14 days ans send email

Hi, We need a script that invokes the sql query every 14 days ans send email (0 Replies)
Discussion started by: bujjisveeru
0 Replies

9. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

10. UNIX for Dummies Questions & Answers

Shell to run query, encrypt and send email

Hello To do : 'automate the process of generating report and send it to client every 15 days after encrypting the report file' Did so far : - generate Java program to encrypt the file. - generate SQL query to run the report. Is there any body who can help me to go further ? ... (7 Replies)
Discussion started by: cub
7 Replies
Login or Register to Ask a Question