Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Problem in sending mail with database content

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Problem in sending mail with database content
# 1  
Old 12-26-2013
Problem in sending mail with database content
Hi All,
I want to fetch records from Oracle DB table and send it in a mail to a set of users. i.e, I have a query which returns a set of records. I want to send mail with below content:



Hi ,
PFB the details:

<first database record>
<Second database record>
…………….
………………..
…………………..
<n database record>




I am using SPOOL command to get result set from db table in a file result.txt and trying to append it with other text (Hi All,……………. ) and send it in mail.
Below is my code but it just creating a file result.txt with result set from oracle table.


#!/bin/sh

EMAIL="anil.kumar@abc.com"
EMAILMESSAGE="/app/to/path/result.txt"
SUBJECT=" testmail"


echo "Hi," >> $EMAILMESSAGE

echo "PFB the Details:" >> $EMAILMESSAGE

sqlplus -s userid@dbname/pwd<<END

SET HEADING OFF ;
SET NEWPAGE NONE ;
SET FEEDBACK OFF ;
SET WRAP OFF ;

spool /app/to/path/result.txt

select distinct entity_id, fund_name from fi_ss_risk_ctry_dts
where rownum<100;

spool off;
exit;

/bin/mail -s "$SUBJECT" "$EMAIL" < $EMAILMESSAGE
rm /app/to/path/result.txt



Where is my code misbehaving. What can be the problem? Please suggest.

Thanks in advance.

Regards,
Anil
# 2  
Old 12-26-2013
Quote:
Originally Posted by anil029
Hi All,
I want to fetch records from Oracle DB table and send it in a mail to a set of users. i.e, I have a query which returns a set of records. I want to send mail with below content:



Hi ,
PFB the details:

<first database record>
<Second database record>
................
....................
.......................
<n database record>




I am using SPOOL command to get result set from db table in a file result.txt and trying to append it with other text (Hi All,................ ) and send it in mail.
Below is my code but it just creating a file result.txt with result set from oracle table.


#!/bin/sh

EMAIL="anil.kumar@abc.com"
EMAILMESSAGE="/app/to/path/result.txt"
SUBJECT=" testmail"


echo "Hi," >> $EMAILMESSAGE

echo "PFB the Details:" >> $EMAILMESSAGE

sqlplus -s userid@dbname/pwd<<END

SET HEADING OFF ;
SET NEWPAGE NONE ;
SET FEEDBACK OFF ;
SET WRAP OFF ;

spool /app/to/path/result.txt

select distinct entity_id, fund_name from fi_ss_risk_ctry_dts
where rownum<100;

spool off;
exit;

/bin/mail -s "$SUBJECT" "$EMAIL" < $EMAILMESSAGE
rm /app/to/path/result.txt



Where is my code misbehaving. What can be the problem? Please suggest.

Thanks in advance.

Regards,
Anil
try below

Code:
#!/bin/sh
EMAIL="anil.kumar@abc.com"
EMAILMESSAGE="/app/to/path/result.txt"
SUBJECT=" testmail"
echo "Hi," >> $EMAILMESSAGE

echo "PFB the Details:" >> $EMAILMESSAGE

sqlplus -s userid@dbname/pwd<<END

SET HEADING OFF ;
SET NEWPAGE NONE ;
SET FEEDBACK OFF ;
SET WRAP OFF ;
spool /app/to/path/result1.txt
select distinct entity_id, fund_name from fi_ss_risk_ctry_dts
where rownum<100;
spool off;
exit;
END
cat /app/to/path/result1.txt >> $EMAILMESSAGE

/bin/mail -s "$SUBJECT" "$EMAIL" < $EMAILMESSAGE
rm /app/to/path/result.txt
rm /app/to/path/result1.txt

# 3  
Old 12-26-2013
Thanks. It worked fine.

Now, there is one more problem that the output in mail is not properly arranged. I want database content to be in tabular format with column headings and center aligned.

Any help in this regard would be highly appreciated.

Regards,
Anil
# 4  
Old 12-26-2013
Try not to set headings OFF, and try to JUSTIFY columns.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Mail sending with multiple attachement(pdf and csv) with html content from Linux

Hi, We have a requirement to send multiple attachment(pdf and csv) along with html content in a single mail. For that we are using uuencode. It is working for single pdf attachment and html content. But we are unable to send both pdf and csv attachment with html content. Below is the script.... (5 Replies)
Discussion started by: dholea
5 Replies

2. Shell Programming and Scripting

Problem in Sending mail as an attachment

Hi, I need to send a email as a txt file and i used the commands, (echo `echo $EMAIL_MSG` ; uuencode "file.txt" "file.txt" ) | mailx -s "$EMAIL_SUBJECT" "$EMAIL_ID" I received email with a attachment with all data but i get all of them in one row. For example: If file.txt contains value... (1 Reply)
Discussion started by: skcvasanth
1 Replies

3. UNIX for Dummies Questions & Answers

problem in sending out content in unix

Hello all!! i got a problem in the email content format in outlook which i mail it from a unix. i want the content to be multiple lines as i see in unix, but the content is grouped into a single line instead in the email content. The email content is made from tail of a file, and echo... (4 Replies)
Discussion started by: boydunshout
4 Replies

4. UNIX for Advanced & Expert Users

problem in sending mail to a group

I am calling a unix script in oracle to send a mail to outlook group using mailx. The cmd looks like this: mailx -s "AA_BBB_" APPostServices@xyz.com APPostServices@xyz.com isa group but the mail is not sent but when i send mail to another group it works fine. What are the things that i... (1 Reply)
Discussion started by: petervuksich
1 Replies

5. UNIX for Dummies Questions & Answers

Problem with sending E-Mail

Im having trouble with sending a file with attachment. The attach file wasn't sent to to the recepient. We tested this manually and it works but with the script it doesn't work. I'm thinking there is a problem with the script. Here is the code: mailto=`/dir/dir2/dir3/GET_EMAIL_ADDR.sh... (9 Replies)
Discussion started by: Iq_zero
9 Replies

6. AIX

Problem sending mail

Everytime i try sending mails using the command line have a reply form the daemon saying : ? 1 Message 1: From MAILER-DAEMON Wed Apr 25 10:42:36 2007 Date: Wed, 25 Apr 2007 10:42:36 -0500 From: Mail Delivery Subsystem <MAILER-DAEMON> To: malarie MIME-Version: 1.0 Content-Type:... (0 Replies)
Discussion started by: Netghost
0 Replies

7. Shell Programming and Scripting

problem sending mail to outlook

i'm a avid fan of making the output of my scripts look pretty. so as a result i put in my scripts a lot of lines that are formed by either ****** or ======= now, outlook seems to have a problem with that. it always removes something that it calls "line breakers" from the emails my scripts send... (5 Replies)
Discussion started by: Terrible
5 Replies

8. UNIX for Dummies Questions & Answers

sending mail with html content

hi, I am new to unix. I need send html content as a mail from my sun-solaris2.6 work station. When I tried that the recipient gets it as html code with all the tags. any solutions? thanx in advance (2 Replies)
Discussion started by: gmchoudary
2 Replies

9. UNIX for Advanced & Expert Users

I have a problem in sending mail

Hi, I have a problem in sending mail ...... #!/usr/bin/perl -w $file_length = `cat file.txt | wc -l`; if ( $file_length == 0) {} else { `mail -s "Tool" <mymailid> < file.txt`; } Eventhough i have used `uuencode Mailfile$App_Server$line | mail <mymailid>`;... (2 Replies)
Discussion started by: vijaysabari
2 Replies

10. Shell Programming and Scripting

I have a problem in sending mail

Hi, I have a problem in sending mail ...... #!/usr/bin/perl -w $file_length = `cat file.txt | wc -l`; if ( $file_length == 0) {} else { `mail -s "Tool" <mymailid> < file.txt`; } Eventhough i have used `uuencode Mailfile$App_Server$line | mail <mymailid>`;... (1 Reply)
Discussion started by: vijaysabari
1 Replies
Login or Register to Ask a Question