Hi,
If I can get some help on this it will be great.
All I am trying to do here is - My shell script runs sqlplus (and connects to oracle using scott and tiger and password). It then runs a scott_query_database.sql file and creates a log file called scott_query_database.log all in the same directory.

Finally my shell script should send this log file to my email mansac@rediffmail.com

My script is doing the first part perfectly. And it creates the log file too(i checked..) However it does not email me anything. This is a little weird because it ran the first two times perfectly when I ran the script....also emailed me the log. Since the 3rd time onwards, it is not doing so. The log gets created but no email. The program wasn't touched so I am not sure what is happening and where to look to troubleshoot in unix(i am on AIX 4.3). (something like where these emails are hitting....or something similar..)

Thanks

My script is
#######################################
sqlplus -s scott/tiger <<-SQL
@scott_query_database.sql
exit
SQL
cat scott_query_database.log | mailx -r "DBUSER@`hostname`" -s "ALERT: A MESSAGE FROM SERVER01 " "mansac@rediffmail.com"
#######################################

Try this:

Thanks for the response.
This didn't work either. The emails are not getting sent. What is mail.msg in the above code ? There is no mail.msg getting created either.

The script woks, I use it every day!

Did you code it correctly? mail.msg should be a temporary file created by the script.
Where does scott_query_database.log come from? Is it from a spool statement in scott_query_database.sql?

Your script should look like this if scott_query_database.log is from a spool statement:

Thanks for your response.
Yes, scott_query_database.log is from a spool statement in the .sql file.

I made the code exactly as your latest post. Ditto the same. However, I am still not able to get it working.. (Either I am going crazy or must have done something silly...., but I just cannot spot where I am going wrong..)
Everything is exactly as you mentioned.
As a single command,
mail mansac@rediffmail.com < scott_query_database.log

works, but not the script.

Any other ideas or suggestions, please if any. Thanks

Post your script

Hello,
Here are the script, sql file and the log file that gets generated.
Thanks for helping me out -----

***Contents of scott_query_database.sql **********************

set feedback off;
set heading off;
spool scott_query_database.log
Prompt The total number of records in emp table is
select count(*) from emp;
Prompt The current value of sequence EMP_SEQ is
select last_number - cache_size "EMP_SEQ"
from user_sequences where sequence_name = EMP_SEQ';
spool off
set heading on;
set feedback on;

************************************************** ****

***Contents of scott_query_database.sh *************************
#!/bin/ksh
sqlplus -s scott/tiger <<-SQL
@scott_query_database.sql
exit
SQL
cat - scott_query_database.log <<! >/tmp/mail.msg
To: mansac@rediffmail.com
Subject: ALERT: A MESSAGE FROM SERVER01
!
mailx -r "DBUSER@`hostname`" -t </tmp/mail.msg

************************************************** ****

**Contents of scott_query_database.log *************************

The total number of records in emp table is

22
The current value of sequence EMP_SEQ is

2998

************************************************** ****

Mine works, this is the e-mail:
And here is the code:
Note that the '-r' user id MUST EXIST! (And have permissions to send e-mails)

Allright ,,,, as a last resort, even though I coded everything exactly as your sh script, I now copied from your posting and pasted to my script and ran it...
it created the log file without emailing me anything but also gave me a message saying

mailx: Not a recognized flag: t

(The line is exactly as yours ....
mailx -r "DBUSER@`hostname`" -t </tmp/mail.msg

And the DBUSER does exist.

What could the problem be ?

Thanks

Out of curiousity, what is your unix flavour/version ? I am trying this script from an AIX but also have a HP-UX on another machine. I haven't tried it on HP-UX yet which I am planning to do now.......

The mailx -t flag is not working there either....
man mailx
does not show the t option. This confuses me the fact that it worked for you.

Solaris 8.

Try it without the -t:

mailx -r "DBUSER@`hostname`" </tmp/mail.msg

Tried that - - got this messsage

The flags you gave make no sense since you're not sending mail.


Thanks

Sorry,

Don't know why mailx is different in AIX and HP/UX, whan flags does man mailx say are valid?

Look for one that does what solaris -t flag does:


-t Scan the input for To:, Cc:, and Bcc: fields. Any
recipients on the command line will be ignored.