I have written a bash script as shown below. The script works when I run it using bash exec_proc_daily_20.sh on the commandline. The log files are created successfully with records inside as a result of the spool $logfile command.
#!/bin/bash
year=`date +%Y`
month=`date +%m`
day=`date +%d`
prefixe=$month$day$year
logfile="/home/oracle/logs/exec_proc_daily_20_"$prefixe.log
sqlplus / "as sysdba" <<EOF >$logfile
spool on
spool $logfile
execute Proc_RFC_MAJ_MV_ITIN;
execute Proc_RFC_MAJ_MV_REFGEO;
commit;
quit
EOF
However, when I schedule the script with crontab -e like below:
* 4 * * * bash /home/oracle/scripts/exec_proc_daily_20.sh
The log file is created but nothing is written to it. Why isn't spool $logfile working in this case?
After reading comments here, I solved the problem. I was suppose to explicitly set variables in the script. below is the working version:
#!/bin/bash
year=`date +%Y`
month=`date +%m`
day=`date +%d`
prefixe=$month$day$year
logfile="/home/oracle/logs/exec_proc_daily_20_"$prefixe.log
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_2
export ORACLE_SID=IMSPROD1
export ORACLE_BIN=${ORACLE_HOME}/bin
export PATH=$PATH:${ORACLE_BIN}
sqlplus / "as sysdba" <<EOF >$logfile
spool on
spool $logfile
execute Proc_RFC_MAJ_MV_ITIN;
execute Proc_RFC_MAJ_MV_REFGEO;
commit;
quit
EOF
As you can see from the code above, I added ORACLE_HOME, ORACLE_SID, ORACLE_BIN and PATH to get things working.