I have the commands that I need. If I execute these commands at the prompt, everything works as expected. SQLPlus runs the query, exports the result to a file and then Mailx emails that file to me.
sqlplus username/pwd@instance
SPOOL /home/sadmin/sqlmail/spool.out
set linesize 2000
set wrap off
@/home/sadmin/sqlmail/query.sql
SPOOL OFF
exit
mail -s "Subject" [email protected] < /home/sadmin/sqlmail/spool.out
But, I can not, for the life of me, figure out how to put these in an .sh file so that I can schedule it. Help!? And thanks in advance, I'm sure this is very silly.
Using a shell 'Here' document is the usual solution, i.e.
cat MyCommand.sh
#!/bin/bash # (or ksh, sh, etc)
PATH=.../path/to/sqlplusdir:${PATH}
sqlplus username/pwd@instance <<EOS
SPOOL /home/sadmin/sqlmail/spool.out
set linesize 2000
set wrap off
@/home/sadmin/sqlmail/query.sql
SPOOL OFF
exit
EOS
mail -s "Subject" [email protected] < /home/sadmin/sqlmail/spool.out
You'll need to set your PATH env to include the path to you sqlplus executable.
Then you need a userID with access to the cron facility. Do you know how to use the 'vi' editor? When you call crontab
, you will be looking at the current users scheduled jobs and you will need to manipulate the file with 'vi' commands. (It may be possible to override which editor to use, but not recommended)
You need to read the man page for cron, i.e. man cron
, and you will be call the above a a complete script. A cron entry will look like
59 23 31 12 * { var=x; export var ; myCommand ; } > /tmp/myWorkDir/myCommand.trace 2>&1
min
hr
day
mon
(DayOfWeek)
values for date/times can be comma separated lists (0,15,30,45), hyphen separated ranges (4-6) or * to indicate all valid values.
This captures any output including stderr, from the 1 time run of myCommand into the file in the tmpDir.
The bare minimum version of the above would be
59 23 12 31 * var=x; export var ; myCommand
and then any output is sent to the user's local mailbox.
I hope this helps.