MAILX Body containing SQL results - UNIX for Dummies Questions & Answers

dstinsman · #1 (**permalink**) 10-05-2006

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

anbu23 · #2 (**permalink**) 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

BOFH · #3 (**permalink**) 10-05-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

Code:

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

But it might not work with your specific shell.

Carl

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
display HTML text in body using unix mailX ????	sparan_peddu	UNIX for Advanced & Expert Users	8	07-29-2008 10:23 PM
Can any body tell me.	sackNumchai	Linux	3	05-05-2008 09:28 PM
Mailx : can we have the body to be a binary file ?	CKIRCH	UNIX for Dummies Questions & Answers	5	01-01-2008 03:33 PM
mailx: concatenating strings for message body (KSH)	rockysfr	Shell Programming and Scripting	1	07-29-2007 05:12 AM
mailx error message : mailx: NUL changed to @	BG_JrAdmin	UNIX for Dummies Questions & Answers	2	12-01-2005 06:27 AM

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode