I need help to write a script.

[Reynaldo Torres]

Please can someone help me to write a script to send mail from some oracle parameters for example.

EFILE= "Is the file or out put report"
BUYER_EMAIL= "The person I need to send the report"
VENDOR_EMAIL= "The e-mail address from the vendor"
PO_NUMBER= "The Purchase Order Number"

These are parameteres set in oracle so when the buyer enter the information in oracle a report gets generated, and once the report is generated resides in the output report writer in UNIX so I want to write a script to be able to extract these parameter and send the e-mail out to whatever vendors needs to go.

If you can help with this, I really appreciated.

Thank you so much.

Reynaldo Torres

[Sridhar Bhaskarla]

Reynaldo,

Do we need to extract the information from Oracle tables or is already there in the output files?

Do we have to send mail to the buyer that is there in the output file?

Your message is not clear. Please explain in detail.

-Sri

[Praveen Bezawada · Accepted solution]

Hi
If the data is in oracle tables.
Assuming that it exists in a single table you can do

#! /sbin/sh
sqlplus login/passwd << INPUT
set heading off echo off termout off pagesize 0 space 0
spool output.lst
select EFILE||','||BUYER_EMAIL||','||VENDOR_EMAIL||','||PO_NUMBER from table_name;
spool off
INPUT

#Now you have the output in the from
#efile,buyer_email,vendor_email,po_number
# in the file output.lst
# In the same file

while read eachline ; do
file=`echo $eachline| awk -F, '{ print $1 }'`
buyer_email=`echo $eachline| awk -F, '{ print $2 }'`
vendor_email=`echo $eachline| awk -F, '{ print $3 }'`
po_number=`echo $eachline| awk -F, '{ print $4 }'`
`/usr/sbin/sendmail $buyer_mail < $file`
done
This send the file contents to the buyer_email.
I am not sure what you want to do with the other two variables.
Hope this helps...
...BPK...

[Jared Westgate_1]

Hello Reynaldo,

I'm not sure I understood your question completely, but it sounds to me that you want to pull the variables from the EFILE? I put together a script, making a TON of assumptions. Hopefully you will find it a little bit useful. If you do want to pull the values from the EFILE, please give us a copy of it so that we can see what format it has.

I think I must have a little too much time on my hands, but here is what I came up with. :)


<<<<<<<<<<<<<<<<<
BUYER_EMAIL=root
BUYER_EMAIL=jwestgate@sunsoftlenses.com
VENDOR_EMAIL=root
VENDOR_EMAIL=jwestgate@sunsoftlenses.com
PO_NUMBER=123456
ITEM1=3423
DESC1=Blue Glow in the dark basketball
QTY1=23
PRICE1=13.00
ITEM2=2342
DESC2=Giant batch of Sea Monkeys
QTY2=3000
PRICE2=Priceless!
TOTAL=41300.00
>>>>>>>>>>>>>>>>>>>

<script>
<<<<<<<<<<<<<<<<<<<<
EFILE="/tmp/testfile"
BUYER_EMAILS=`cat $EFILE | grep "BUYER_EMAIL" | cut -f 2 -d "="`
VENDOR_EMAILS=`cat $EFILE | grep "VENDOR_EMAIL" | cut -f 2 -d "="`
PO_NUMBER=`cat $EFILE | grep "PO_NUMBER" | cut -f 2 -d "="`
POMAILFILE="/tmp/pomail.tmp"

echo $BUYER_EMAILS
echo $VENDOR_EMAILS

for BUYER_EMAIL in $BUYER_EMAILS
do
echo "
Thank you for shopping with us!
Your order has been placed.
" | mailx -s "Thank you for your purchase" $BUYER_EMAIL
done

for VENDOR_EMAIL in $VENDOR_EMAILS
do

echo "
We would like to purchase the following items.
Please find the attached Purchase Order,
PO# $PO_NUMBER

" > $POMAILFILE
cat $POMAILFILE $EFILE | mailx -s "Purchase Order $PO_NUMBER" $VENDOR_EMAIL
done
>>>>>>>>>>>>>>>>>

Ok, ok... So this is probably completely useless to you, but I hope there is a slim chance you'll find what you need here. If you can give us any more information, it would be great.

Best of luck!

Jared

[Reynaldo Torres]

Thanks to all of you who sent me examples on how to write scripts they really helped me to get the idea to write the script that I needed in order to pull the data as well to send the e-mail.

Thanks again,

Reynaldo.