E-mail the result of an SQL Statement?

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
DrewOZAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yokelCommented:
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
DrewOZAuthor Commented:
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
ahoffmannCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

PrintapornCommented:
HI,
sqlplus user/password@tnsstring << EOF | uuencode result|mailx -s "result" user@domain
select ....
EOF
0
DrewOZAuthor Commented:
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
ahoffmannCommented:
> .. 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DrewOZAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Unix OS

From novice to tech pro — start learning today.