Thank you to everyone for your recommendations.
I hacked a solution together. It may not be as pretty as some of the
suggestions I received, but it's simple and it works.
Here's the CRON table entries:
05 0 * * * /ods/live/scripts/rowcount.sh
10 0 * * * /bin/cat /ods/live/scripts/oa_count.txt | mail -s "OA Row Counts"
emailATdomainDOTcom
Here is the rowcount.sh script:
#!/bin/bash
export ORACLE_HOME="/opt/oracle/ora92"
export ORACLE_SID="sid"
$ORACLE_HOME/bin/sqlplus user/password @/ods/live/scripts/oacount.sql
Here is the oacount.sql script (called by rowcount.sh):
connect user/password
set trimspool on
spool /ods/live/scripts/oa_count.txt
SELECT COUNT(*) "SV_OR_601_OA_OTH_OTH Row Count",
TO_CHAR(SYSDATE,'fmMM/DD/YYYY fmHH24:MI:SS') "Date/Time"
FROM ods.sv_or_601_oa_oth_oth;
spool off
exit
Josh Graves
http://www.thegeeksite.com
http://www.geneamap.com
----- Original Message -----
From: "Walker Jed S "
<oracledba-ezmlmshield-x99175885.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Wednesday, March 01, 2006 10:22 AM
Subject: RE: E-Mailing Output
> Of course you'll need to configure your environment in the script (PATH,
> ORACLE_HOME, ORACLE_SID, etc) because cron doesn't setup your
> environment.
>
> -----Original Message-----
> From: Walker Jed S
> [mailto:oracledba-ezmlmshield-x97507381.[Email address protected]
> Sent: Wednesday, March 01, 2006 9:15 AM
> To: LazyDBA Discussion
> Subject: RE: E-Mailing Output
>
> 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
>
>
>
> --------
> 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
>
>
>
> --------
> 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