?
Solved

E-mail the result of an SQL Statement?

Posted on 2003-03-17
7
Medium Priority
?
643 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
Java performance on Solaris - Managing CPUs There are various resource controls in operating system which directly/indirectly influence the performance of application. one of the most important resource controls is "CPU".   In a multithreaded…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.
Suggested Courses
Course of the Month9 days, 23 hours left to enroll

762 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