Hi,
i need to send emails to the mail ids i receive using the sql query .
SELECT L.ALARM_DEF,L.CRITERIA,L.DISTANCE_METRE,L.EMAIL,L.LAC_ID,L.LAST_CHECK,L.RANGE,
L.RESOURCE_MSISDN,LM.ADDRESS,LM.NAME FROM LANDMARK_ALARM_CONF l, LANDMARK lm
WHERE L.LANDMARK_ID=LM.LANDMARK_ID AND L.ALARM_STATUS=1 and l.NOTIFY_STATUS is null
i will be getting multiple number of rows of data for this query, and i need to send mail to each row of data with the content received for that row.
sample data
i need to send the email to say for e.g aemunathan.r@gmail.com --first row of data......
echo "content has to be fetched from ALARM_DEF and append the data of DISTANCE_METRE and LAST_CHECK column of sql query output" | mailx -r "myname" -s "value of L.RESOURCE_MSISDN,LM.ADDRESS,LM.NAME appeneded in such a way to form sentence...for e.g $RESOURCE_MSISDN reached the landmark $NAME $ADDRESS at $LAST_CHECK " aemunathan.r@gmail.com
this way i need to snd to all records and update the NOTIFY_STATUS column in my table to 1 once mail sent, so that same mail wont be sent again... .
pls guide me how to proceed...
---------- Post updated at 08:41 AM ---------- Previous update was at 12:14 AM ----------
Hi,
i tried the following way...but not tasted success...
#!/bin/ksh
export ORACLE_HOME=/u01/app/oracle/product/9.2.0
export PATH=$PATH:$ORACLE_HOME/bin
list=`sqlplus -S <<EOF
username/pwd@sid
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF ECHO OFF SERVEROUT ON TIME OFF TIMING OFF
SELECT L.ALARM_DEF ||',' || L.CRITERIA || ',' || L.DISTANCE_METRE || ',' || L.EMAIL || ',' || L.LAC_ID || ',' || L.LAST_CHECK
|| ',' || L.RANGE || ',' || L.RESOURCE_MSISDN || ',' || LM.ADDRESS || ',' || LM.NAME FROM LANDMARK_ALARM_CONF L ,LANDMARK LM
WHERE L.LANDMARK_ID=LM.LANDMARK_ID AND L.ALARM_STATUS=1 and L.NOTIFY_STATUS is null;
EOF
`
for item in $list
do
echo "$list"|while IFS="," read f1 f2 f3 f4 f5 f6 f7 f8 f9 f10
do
echo "Dear Customer, The resource with mobile number $f8 has entered $f2 the range of $f7 from the landmark name defined as $
f10 with address as $f9 at $f6. The approximate distance is $f3 metres. " | mailx -r "care@care.com" -s "$f1" $f4
done
done
exit 0
error thrown is