Shell Programming and Scripting

I want to send email for every row comes out of following SQL statement
thank you for your help.

*****SQL STATEMENT******
Select SCUSER AS "USER IDS" , SCEUSER AS "LOCKED OUT" FROM SYS.7333.F98OWSEC;

*******OUPUT COMES LIKE THIS AND ONE EMAIL COMES AS PER SCRIPT BELOW******

******BUT MY REQUIREMENT IS A SEPRATE EMAIL FOR EVERY LOCKED ID/ROW****

Jun 25 2009 06:00:02
"Following System User Ids Locked Out"
User Id
----------
LJOHNSTON
CTONOLETE
MEDWARDS
MLOVELESS
SALTOSJ

****EMAIL SCRIPT*********
#!/bin/sh
. /home/oracle/.cron_profile
#
export LOGDIR=/home/oracle/log
cd /home/oracle
#
sqlplus /nolog @/home/oracle/Daily_lockedout.sql
#
cat $LOGDIR/Daily_lockedout.log >>$LOGDIR/Daily_lockedout.all.log

uuencode /home/oracle/log/Daily_lockedout.log Daily_lockedout.log |mail -s "Daily_lockedout.log" jxx@TGT.com;

And what are those emails ? E.g. what is the email corresponding to the first id "LJOHNSTON" ? And that corresponding to "CTONOLETE" ? etc.

Code:

...
LJOHNSTON
CTONOLETE
MEDWARDS
MLOVELESS
SALTOSJ
...

Do you want to send the entire log file which contains this -

Code:

$
$ cat locked.log
LJOHNSTON
CTONOLETE
MEDWARDS
MLOVELESS
SALTOSJ
$

to the following list ?

Code:

LJOHNSTON,CTONOLETE,MEDWARDS,MLOVELESS,SALTOSJ

If yes, then you can aggregate the file content into a comma-delimited string like this -

Code:

$
$ addr_list=$(awk '{x=x$0","}END{sub(/,$/, "", x); print x}' locked.log)
$
$ echo $addr_list
LJOHNSTON,CTONOLETE,MEDWARDS,MLOVELESS,SALTOSJ
$

And then use the "addr_list" variable in your mail command.

HTH,
tyler_durden

Modify the sql to generate the mailx commands in the spool file and then execute the commands in the shell script:

Modified SQL Code:

Code:

SELECT    'mailx -s " Locked out Info: user id -  '
       || scuser
       || ' user name - '
       || sceuser
       || '" jxx@TGT.com </dev/null'
  FROM SYS.7333.F98OWSEC;

Modified Script (Not Tested):

Code:

****EMAIL SCRIPT*********
#!/bin/sh
. /home/oracle/.cron_profile
#
export LOGDIR=/home/oracle/log
cd /home/oracle
#
sqlplus /nolog @/home/oracle/Daily_lockedout.sql
#
cat $LOGDIR/Daily_lockedout.log >>$LOGDIR/Daily_lockedout.all.log

while read myLine
do 
   eval ${myLine}
done<$LOGDIR/Daily_lockedout.log

You can customize the mail subject line in the sql as per you requirement... Hope this helps...

regards,
Arun.

****And what are those emails ? E.g. what is the email corresponding to the first id "LJOHNSTON" ? And that corresponding to "CTONOLETE" ? etc.***

I want seprate email for every locked out account to be send to help desk for work order creation. Unfortunatlet they require sperate email per user instead of one combile list. So seprate email for LHOHNSON id to help@xyz.com & one email from system for CTONOLETE locked id to help@xyz.com. So task is to send daily individual email for ever locked out user to help desk/

any thoughts?

Thanks

That's exactly what the code that I suggested in my previous post does... Did you try that?

regards,
Arun.