MAILX Body containing SQL results


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers MAILX Body containing SQL results
# 1  
Old 10-05-2006
MAILX Body containing SQL results

I have a KSH script that runs a SQL script, then sends an email to me indicating that it's completed. I would like to modify it so that the body of the email will contain a count of the number of records Added, Changed, Deleted as a result of the SQL script.

Here's what I was trying, but it's not working...what am I doing wrong?
Code:
MESG_GOOD="Completed Successfully"

cntADD=sqlplus -s user/pwd select count(*) from tbl where...;
cntCHA=sqlplus -s user/pwd select count(*) from tbl where...;
cntDEL=sqlplus -s user/pwd select count(*) from tbl where...;

content="Adds: " && cntADD && "Changes: " && cntCHA && "Deletes: " &&cntDEL

/usr/bin/echo $content | /usr/bin/mailx -s $MESG_GOOD me@here.com


Last edited by dstinsman; 10-05-2006 at 04:50 PM..
# 2  
Old 10-05-2006
Quote:
cntADD=sqlplus -s user/pwd select count(*) from tbl where...;
cntCHA=sqlplus -s user/pwd select count(*) from tbl where...;
cntDEL=sqlplus -s user/pwd select count(*) from tbl where...;
I think above code won't do what you intended to do.

try this

Code:
sqlplus -s user/pwd <<EOF
spool /res.lst
select count(*) from tbl where..;
select count(*) from tbl where..;
spool off
EOF

Code:
sed -n "/--*/{n;p;}" res.lst | tr '\n' ' '| read cntADD cntCHA cntDEL

# 3  
Old 10-06-2006
I don't know about the SQL commands themselves however if you want to assign the output of a command to a variable, you need to use the backtick marks.

Code:
cntADD=`sqlplus -s user/pwd select count(*) from tbl where...;`
cntCHA=`sqlplus -s user/pwd select count(*) from tbl where...;`
cntDEL=`sqlplus -s user/pwd select count(*) from tbl where...;`

Since I write to multiple environments, I typically drop to the lowest common denominator so you could also use these instead of backticks but backticks are universal Smilie

Code:
cntADD=$(sqlplus...)
cntCHA=$(sqlplus...)
cntDEL=$(sqlplus...)

But it might not work with your specific shell.

Carl
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Mailx with attachment and message body

i have to attach the 'body in the email' along with attachment below code is throwing errors, how can i do it ? here body file contains message, it should display in email, please help i am using HP-UX (cat body ;) (uuencode attch1 attch1 ;) | mailx -m -s "testing" "abc@gmail.com" ... (4 Replies)
Discussion started by: only4satish
4 Replies

2. Shell Programming and Scripting

Need to print body of content using mailx

Hi, I'm a perl developer. I need to attach a file from my perl cgi script and send mail to our users whoever using our website. I have used mailx command to attach a file.. it is working fine for me. The problem I'm facing is I'm not getting body of the mail. I have a function called... (0 Replies)
Discussion started by: bheeshmaraja
0 Replies

3. Shell Programming and Scripting

The body of the Email -- mailx

Hi, I am using mailx command to send an email and i took the body of the email from a file, mailx -r gtt.org -s "Status Report " ss@org.com < $ProcessStatisticsFile but now i want to declare the body of the email in the command itself. I have tried with the following command but... (4 Replies)
Discussion started by: chinnu01
4 Replies

4. UNIX for Dummies Questions & Answers

Mailx empty body message

Hi, Is there a way to suppress this message? Null message body; hope that's ok My email string is: mailx -s "This is my subject" myemail@domain.com < /dev/null It's just an annoyance to me that I would like see go away. (3 Replies)
Discussion started by: bbbngowc
3 Replies

5. Shell Programming and Scripting

send attachment and body in one mail using mailx

Hi, Our requirement is to send an attachment and content in a single mail. I am using the below command to send attachement. --------------------- (uuencode $exp_file $exp_file) |mailx -s "$email_subject" $EmailRecipients -------------------- I m not able to send any message in the... (4 Replies)
Discussion started by: ashwin3086
4 Replies

6. UNIX for Dummies Questions & Answers

mailx and html body messages

Hi, I'm working on Solaris 9 (SPARC) and I like to send an html body message to our users when something happen. The problem is that I can't find how to give mime type information with mailx. To be recognized as html I need to put in email header this information: Mime-Version: 1.0... (2 Replies)
Discussion started by: gbagagli
2 Replies

7. Solaris

mailx on solaris - How to add mail body

hi, Can anyone please tell me how to add some text in the mail body like I can add subject using the following syntax. mailx -s "Hi - This is mail subject" xyz@abc.com Many Thanks. (4 Replies)
Discussion started by: abovais
4 Replies

8. UNIX for Advanced & Expert Users

display HTML text in body using unix mailX ????

display HTML text in body using unix mailX ????Hello, could any one tell me how to display text in html layout by sending a file using mailx command in unix. i know to use mailx : mailx -s "SUBJECT" user.name@domail.com < file_name.txt instead of txt file i want to send html page and... (8 Replies)
Discussion started by: sparan_peddu
8 Replies

9. UNIX for Dummies Questions & Answers

Mailx : can we have the body to be a binary file ?

Hi I am totally dummy as far as UNIX is concerned, so please apologize. I was just given the syntax to send multiple attachments with a body.txt message, it works great and we use it a lot. Now, my question is : can we have the body to be a Binary file (like a Word Document for example)? ... (5 Replies)
Discussion started by: CKIRCH
5 Replies

10. Shell Programming and Scripting

mailx: concatenating strings for message body (KSH)

Hi all, Think this is a pretty simple problem, but I've been thinking about it for a few days. Let's say that I'm going to have to output the contents of a file as the body of a mailx message. I'll probably do this: cat <filename> | mailx <extra commands> However, how do I go about doing... (1 Reply)
Discussion started by: rockysfr
1 Replies
Login or Register to Ask a Question