Hi

Can somebody help me in wirting shell script in the following way.Desperately i have poseted this..

1.How can I connect to Oracle(Sqlplus) from UNIX.
2.After connecting to it I have to execute an oracle proceudure..
3.Execution of the procedure will give some set of rows..
4.The set of rows have to be 'Spool' to a seperate file and that file has to be sent to particular user as attachement.

Regards
Gopinath

echo "Hi\n\n This is mail body \n\n Thanks & regards \n Myself." > /tmp/$$.mailbody

sqlplus -s $CONNECT_STRING <<EOF > /tmp/$$.attachment

your pl/sql block here to call the stored procedure

exit;
EOF

uuencode $$.attachment results.txt >> /tmp/$$.mailbody

mailx -s "SUB: Stored procedure results" yourname@email.com < /tmp/$$.mailbody

rm /tmp/$$.mailbody
rm /tmp/$$.attachment

do some checks with the results file to check that the stored procedure completed successfully or failed... otherwise you will get a email with the sql error in case if it failed.

hi mahendra ,

thanks a lot for your reply...i am not able to get "$$.mailbody" and "$$.attachment". could u please explain me what does these means.

regards
gopi

Here is what I do. I simplified the code and remove the variables so you can understand better.

echo "To: user@mail.com" > mail.tmp
echo "From: your@mail.com" >> mail.tmp
echo "Subject: your subject" >> mail.tmp
echo "Your text" >> mail.tmp

uuencode path/attached_file attached_file >> mail.tmp
cat mail.tmp | /usr/sbin/sendmail -t

$$ is nothing but a process id

The funda behind using $$ infront of file name is, if two people executing the same script, it won't overwrite others and end...

I'm just starting the file name with the process id... assuming the file will be unique if I include the process id in the file name...

Hi mahendra ,

The script you gave me worked clearly with no alterations...My v.v.special thanks to you.

Have a nice days..

Regards
gopinath

Closing the thread