Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

E-mail the result of an SQL Statement?

Posted on 2003-03-17
7
Medium Priority
?
646 Views
Last Modified: 2010-04-21
I'm trying to write a unix script that will be run once a day, and will send e-mails based on the result of an SQL statement. At the moment, I'm not really concerned about the running once a day part, I just want to get the e-mail part working. I have almost no experience with unix, but I'm a quick learner and have been doing some research. Here's the script I've written at the moment:

sqlplus -s mde_user/****@auam33pd <<SQL_END
set head off
set feedback off
set pages 0
set lines 500

SELECT
        'mailx -s ' || chr(39) || 'TEST' || chr(39) || ' drewwalkeris@alwaysonline.net.au||
        chr(13) || chr(10) ||
        'Hi ' || emp.emp_first_name || ',' ||
        chr(13) || chr(10) ||
        chr(13) || chr(10) ||
        'The following sales opportunity has been ' || opp.opp_status || ': ' || opp.opp_name ||
        chr(13) || chr(10) ||
        'Please visit the link below and fill out a Win/Loss Report.' ||
        chr(13) || chr(10) ||
        chr(13) || chr(10) ||
        'http://ausgood/drewtest/winloss/report.asp?dek=' || opp.dek_opportunity ||
        chr(13) || chr(10) ||
        chr(13) || chr(10) || 'Thank You' ||
        chr(13) || chr(10) ||  
        chr(13) || chr(10) ||                            
        '======================================================' ||            
        chr(13) || chr(10) ||
        'This is an automated email. Please do not reply to it.' ||
     chr(13) || chr(10) || '.' ||
        chr(13) || chr(10)
FROM
     mde_user.opportunity opp, mde_user.ad_user adusr, mde_user.employee emp
WHERE
        opp.dek_opportunity IN
          (SELECT dek_opportunity FROM opportunity WHERE dek_opportunity NOT IN
            (SELECT dek_opportunity FROM win_loss_compare)
          AND (opp_status = 'Won' OR opp_status = 'Lost')
          AND dek_opportunity LIKE 'H%'
          AND opp_total_revenue > 50000
          AND dek_currency_list IS NOT NULL)
        AND (opp.dek_ad_user = adusr.dek_ad_user
        AND adusr.dek_employee = emp.dek_employee);

SQL_END

Here's the output it gives me:

mailx -s 'TEST' drewwalkeris@alwaysonline.net.au
Hi Justin,                                                                      
                                                                               
The following sales opportunity has been Won: 4F002 - Otago Poly                
Please visit the link below and fill out a Win/Loss Report.                    
                                                                               
http://ausgood/drewtest/winloss/report.asp?dek=HAD0006X3                       
                                                                               
Thank You                                                                      
                                                                               
======================================================                          
This is an automated email. Please do not reply to it.                          
.

The only difference is that it returns the above result for the number of records it finds. This doesn't actually send an e-mail though, it just prints that result on the screen. How would I get it to e-mail the result?

Drew
0
Comment
Question by:DrewOZ
7 Comments
 
LVL 3

Expert Comment

by:yokel
ID: 8157832
Seems a strange way to do it myself. Why don't you just
change the SQL to just return the body of the e-mail message and put it into a script. The pipe the results of this script into the mailx command ie.

./sql_script | mailx -s 'Test' drewwalkeris@alwaysonline.net.au



Your code above does exactly what I would expect, it just prints out the text.."mailx -s ....."
0
 

Author Comment

by:DrewOZ
ID: 8158101
The only reason it was a strange way to do it is because I only started using unix about 2 weeks ago, so I had no idea what I was doing. :)

I figured there was a that you could pipe the output into mailx somehow, but I had no idea where to start. I'll give it a go and then accept your answer.

Thanks Heaps,
Drew
0
 
LVL 51

Expert Comment

by:ahoffmann
ID: 8159311
sqlplus -s mde_user/****@auam33pd <<SQL_END | mailx -s'your subject here' drewwalkeris@alwaysonline.net.au
set head off
set feedback off
set pages 0
set lines 500
SELECT
 ....
SQL_END
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:Printaporn
ID: 8164472
HI,
sqlplus user/password@tnsstring << EOF | uuencode result|mailx -s "result" user@domain
select ....
EOF
0
 

Author Comment

by:DrewOZ
ID: 8170201
Ahoffman,

I tested your solution, and it does work more than my method, but it sends all the records in the one e-mail. I think that's what all the solutions will do (correct me if I'm wrong).

I want it to send an e-mail for each record that is returned. This is very important, because the e-mail address is going to be picked up by the select statement for each record. At the moment it's just hardcoded for testing purposes. That's why in my first post i have 'mailx -s...' in the SELECT statement.

Thanks for you help so far, you've been very helpful, looking forward to hearing a few more ideas.

Drew
0
 
LVL 51

Accepted Solution

by:
ahoffmann earned 225 total points
ID: 8172294
> .. but it sends all the records in the one e-mail ..
yes.

> .. that's what all the solutions will do ..
yes.

one email per selected row:

1. write all your sql statements in a script, for example xyz.sql
2. assuming that the e-mail address is first word in each selected row, try 3.
3. #! /bin/sh
   for line in `sqlplus -s mde_user/****@auam33pd @xyz.sql`; do
     to=`echo "$line"|awk '{print $1'}`
     echo "$line" |mail -s"your subject" $to
   done

4. enjoy
0
 

Author Comment

by:DrewOZ
ID: 8173022
Thanks for the reply ahoffman, but I've figured out a work around:

sqlplus -s mde_user/mde_user@auam33pd >outputfile.sh <<SQL_END
set head off
set feedback off
set pages 0
set lines 500

SELECT
        'mailx -s ' || chr(39) || 'Win/Loss Report' || chr(39) ||
        ' drewwalkeris@alwaysonline.net.au <<MSGEND' || chr(13) || chr(10) ||
        'Hi ' || emp.emp_first_name || ',' ||
        chr(13) || chr(10) || chr(13) || chr(10) ||
        'The following sales opportunity has been ' || opp.opp_status || ': ' || opp.opp_name ||
        chr(13) || chr(10) ||
        'Please visit the link below and fill out a Win/Loss Report.' ||
        chr(13) || chr(10) || chr(13) || chr(10) ||
        'http://ausgood/drewtest/winloss/report.asp?dek=' || opp.dek_opportunity ||
        chr(13) || chr(10) || chr(13) || chr(10) || 'Thank You' ||
        chr(13) || chr(10) ||  chr(13) || chr(10) ||                            
        '======================================================' ||            
        chr(13) || chr(10) ||
        'This is an automated email. Please do not reply to it.' ||
        chr(13) || chr(10) || 'MSGEND' || chr(13) || chr(10)
     MESSAGE
FROM
     mde_user.opportunity opp, mde_user.ad_user adusr, mde_user.employee emp
WHERE
        opp.dek_opportunity IN
          (SELECT dek_opportunity FROM opportunity WHERE dek_opportunity NOT IN
            (SELECT dek_opportunity FROM win_loss_compare)
          AND (opp_status = 'Won' OR opp_status = 'Lost')
          AND dek_opportunity LIKE 'H%'
          AND opp_total_revenue > 50000
          AND dek_currency_list IS NOT NULL)
        AND (opp.dek_ad_user = adusr.dek_ad_user
        AND adusr.dek_employee = emp.dek_employee);

SQL_END

chmod 777 outputfile.sh
ksh outputfile.sh

Works perfectly. Thanks for your help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you do backups in the Solaris Operating System, the file system must be inactive. Otherwise, the output may be inconsistent. A file system is inactive when it's unmounted or it's write-locked by the operating system. Although the fssnap utility…
Using libpcap/Jpcap to capture and send packets on Solaris version (10/11) Library used: 1.      Libpcap (http://www.tcpdump.org) Version 1.2 2.      Jpcap(http://netresearch.ics.uci.edu/kfujii/Jpcap/doc/index.html) Version 0.6 Prerequisite: 1.      GCC …
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question