asked on

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

yokel

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 ....."

DrewOZ

ASKER

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

ahoffmann

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

Printaporn

HI,
sqlplus user/password@tnsstring << EOF | uuencode result|mailx -s "result" user@domain
select ....
EOF

DrewOZ

ASKER

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

ASKER CERTIFIED SOLUTION

ahoffmann

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DrewOZ

ASKER

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.