Shell script to email results of SQLPlus query usi

Question

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" [e-mail] < /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.

Answer 1

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" [e-mail] < /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.