Export SQL results to .TXT file for emailing

Shell Programming and Scripting
1

Hi everyone,
I am new to unix and bash and in need of some help.
I am writing a script that will execute a SQL query. The script runs and the SQl query runs, but I cannot figure out how to save the results as a file that can be emailed to a user. Here is my scripts thus far:

#!/bin/sh
SID=$1
DIR=/u01/test/testcomn/admin/scripts/requestedqueries/
ORACLE_SID=$SID; export ORACLE_SID
ORACLE_HOME directories to be searched by the .sql statement
LD_LIBRARY_PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
cd $DIR

sqlplus user/PWD @export_icat.sql
echo 'Today's Results'  | mail -s "Results" user@domain.com

Thanks!

2

Hi!
I'm not really familiar with Oracle. Is the file export_icat.sql a file containing commands for sqlplus? Where does the output go? To the screen (stdout)?
Then You can probably redirect it to a file, let's call it export_icat.out.

Your mail command sends a mail to user@domain.com, with the Subject "Results" and with the body content "Today's Results". Maybe not even that, depending on what the quotes are. If You want to send the content of the text file export_icat.out as the mail body, use:

...
sqlplus user/PWD @export_icat.sql > export_icat.out
mail -s "Results" user@domain.com < export_icat.out
...

Best regards,
Lakris

PS There are other commands in Your script that won't work as it is written.
Maybe

ORACLE_HOME directories to be searched by the .sql statement
LD_LIBRARY_PATH

should be replaced with something that actually sets these variables?

3

That worked exactly like I needed it to.

As for the two lines that should not have been there, they were actually commented out and I failed to delete them when I was pasting the code in the question.
Thanks SO much!