Visit The New, Modern Unix Linux Community


sending mail using Mailx with data from oracle table


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting sending mail using Mailx with data from oracle table
# 1  
sending mail using Mailx with data from oracle table

Hi,

i need to send emails to the mail ids i receive using the sql query .

Code:
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
Quote:
ALARM_DEF CRITERIA DISTANCE_METRE EMAIL LAC_ID LAST_CHECK RANGE RESOURCE_MSISDN ADDRESS NAME
went out OUT 146.321857 aemunathan.r@gmail.com 40 16-Apr-10 17:24:01 100 919191919191 HRD OFFICE
Aemu Left the office OUT 1167.764448 41 16-Apr-10 19:12:01 1000 919191919191 HRD OFFICE
vanthuru vanthuru IN 146.321857 l@yahoo.com 39 16-Apr-10 17:24:01 1000 919444008844 HRD OFFICE
i need to send the email to say for e.g aemunathan.r@gmail.com --first row of data......

Code:
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...

Code:
#!/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
Quote:
The flags you gave are used only when sending mail.
Usage: mailx -eiIUdFntBNHvV~ -T FILE -u USER -h hops -r address
-s SUBJECT -f FILE users
The flags you gave are used only when sending mail.
Usage: mailx -eiIUdFntBNHvV~ -T FILE -u USER -h hops -r address
-s SUBJECT -f FILE users
The flags you gave are used only when sending mail.
Usage: mailx -eiIUdFntBNHvV~ -T FILE -u USER -h hops -r address
-s SUBJECT -f FILE users

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #681
Difficulty: Medium
In 2011, the Solaris 11 kernel source code was leaked to WikiLeaks.
True or False?

10 More Discussions You Might Find Interesting

1. Red Hat

Problems sending mail: Difference between Mail and Mailx?

Whats the difference between mail and mailx? I'm trying to troubleshoot a problem where I can send mail from server A with this `echo $MESSAGE | mail -s "$SUBJECT" -r $FROM $RECIPIENTS` command but executing the same command from server B throws me this error (Both servers are RHEL) ... (1 Reply)
Discussion started by: RedSpyder
1 Replies

2. Shell Programming and Scripting

Mailx command - send mail as table format

I have to send a couple of rows that have been returned from a SQL query. I have written the output of the query to a file and while i try to print this in the mail body the formatting goes wrong. Intended Output in mail body: Col1 Col2 Col3 ------ ... (2 Replies)
Discussion started by: qwertyu
2 Replies

3. UNIX for Dummies Questions & Answers

Mailx working but not sending mail

Hi All, I have a unix server on which mailx is configured . Earlier i was getting proper mails but now some mails are getting stuck and i am not receiving the emails from the server. I can see the mails in mailq. Can you please help me with this . How can i receive these emails? (2 Replies)
Discussion started by: pratikm23
2 Replies

4. Shell Programming and Scripting

Importing data from PL/SQL then sending it through mail,HELP ME!

Is anyone here know how to make a script in UNIX which will do importing data from PL/SQL then sending it through mail? Can you give me sample script with explanation so it's easy to understand,Thank you very much,any suggestion or advice is welcome, (1 Reply)
Discussion started by: Atrap
1 Replies

5. Shell Programming and Scripting

How to embed a html file in a mail sending from Linux box with uuencode or mailx?

How to embed a html file as subject in a mail sending from Linux box with uuencode or mailx or any other way? we do not want the file as attachment, it should be embedded in the mail subject. (2 Replies)
Discussion started by: johnveslin
2 Replies

6. Shell Programming and Scripting

mailx -s not sending the file to mail address

Hi All, OS:Red Hat Linux 4 86x64 Below is my shell script which is not sending mail to the mail recipient: #!/bin/bash export MAILLIST="xyz@yahoo.com" cd <path_to_the_script_perf_report.sql> sqlplus / as sysdba @perf_report.sql if then cat <path_to_the_script/*MONTHLY*REPORT*.lst... (6 Replies)
Discussion started by: a1_win
6 Replies

7. Shell Programming and Scripting

sending mail through mailx command

Hi I need help to send mail through mailx command . Currenlt my program is like #!/bin/ksh -x echo " " >> path.lst MAIL_LIST='someone1@gamil.com someone2@gamil.com someone3@gamil.com' cat path.lst | mailx -s "path loaction" $MAIL_LIST echo "End" exit 0 Its work fine . But i... (3 Replies)
Discussion started by: mani_isha
3 Replies

8. Shell Programming and Scripting

sending mail from perl using mailx

hi All, i am trying to send a mail from perl using mailx. the script runs fine if i hard code the file path which is to be attached to the mail. but when i give a variable containing the path of the file , perl is not able to send the file. ex: system('uuencode /attach/test.txt... (3 Replies)
Discussion started by: adityamahi
3 Replies

9. UNIX for Dummies Questions & Answers

sending mail using mailx command

Hi All, Please help me regarding sending mails using mailx command in unix. I will be glad if some one give me the sintax of this. I tried using the below but vain. mailx -s 'Hi' 'xyz@abc.com' Regards, Manas (5 Replies)
Discussion started by: manas6
5 Replies

10. Shell Programming and Scripting

sending mail through mailx

while trying to send a mail, though the output file doesn't contain any exclamatory mark(!), i am getting that in the mail with an extra line. please help me out from this ASAP. (3 Replies)
Discussion started by: pvamsikr
3 Replies

Featured Tech Videos