How do I run a specific shell script in a cron job?

Question

I have set up a cron job which will run a shell script to connect to our Oracle database and copy a table as a csv file.

crontab -l outputs:

*/5 * * * * /home/user/import.sh > /home/user/importLog.txt

import.sh looks like this:

#!/bin/bash
FILE="/path/to/application/TABLE.csv"

sqlplus -s user/pw@db <<EOF

SET PAGESIZE 50000
SET COLSEP ","
SET LINESIZE 200
SET FEEDBACK OFF

SPOOL $FILE

SELECT * FROM TABLENAME;

SPOOL OFF
EXIT
EOF

Now, when I run import.sh manually, it successfully imports the table and creates the csv file, whereas the cron job seems to run, as it generates importLog.txt(which is blank), but it does not ever create a csv file.

Answer 1

Cron jobs run with a very "impoverished" environment. You might need to set up environment variables to locate your tnsnames config files. You're also expecting sqlplus to be on the command path, which it may not be if you haven't set it up in the crontab previously -- I highly recommend putting the full path to the sqlplus binary in your script. Finally, add

MAILTO=your@email.addr

early on in your crontab to make sure you get an email with the error notification (assuming your sendmail config is set up correctly).