Hi Josh,

Just create a shell script and do the following:


sqlplus /nolog -s <<EOF
connect me/secret
spool /tmp/it.txt
select ......;
spool off
exit
EOF
cat /tmp/it.txt | mail -s "the stuff" [emailaddressnotknown]

(note: I just typed this so there might be a bug or two)

Also, the benefit to this is your logic is all in a script and if you
lose the cron and the contents of it you still have your code checked in
somewhere.

Alternately, you could put this in a stored procedure / job and use
utl_smtp to do the mailing, but that's going to take more work and
probably isn't worth it in this case.

I hope this helps,

Jed.

-----Original Message-----
From: Josh Graves
[mailto:oracledba-ezmlmshield-x7872573.[Email address protected]
Sent: Wednesday, March 01, 2006 8:40 AM
To: LazyDBA Discussion
Subject: E-Mailing Output

I'm relatively new to Oracle (just now taking my Oracle DBA
certification
classes for 9i), so please be gentle.

I have an Oracle 9i database on a Linux box and I want to e-mail the
output
of a simple SQL script on a daily basis.

SQL script example:

SELECT COUNT(*)
FROM table;

I can e-mail other Linux command line tasks by adding '| mail -s
"subject"
[Email address protected] after the command.

For example, my CRON table on a different Linux box has the entry:

0 0 * * * su - cyrus -c quota | mail -s "Account Quotas
(mail.domain.com)"
[Email address protected]

Which mails the e-mail account quotas to me on a nightly basis.

I am having difficulty getting the output from the SQL script to e-mail
from
CRON. Any suggestions would be greatly appreciated.

Thank you.

Josh Graves
http://www.thegeeksite.com
http://www.geneamap.com



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html


Oracle LazyDBA home page