3

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?

Fokwa Best
  • 3,322
  • 6
  • 36
  • 51
  • May `sqlplus` is not found in `PATH`. Check value of `PATH` environment variable when cron is executed. Usually you have much less definition than you log on manually. – Wernfried Domscheit Oct 25 '16 at 15:39
  • Hi @WernfriedDomscheit. I think sqlplus is found in path. I am able to login on command line. I am in linux environment and login like `$sqlplus / as sysdba`. – Fokwa Best Oct 25 '16 at 15:49
  • 1
    Cron jobs don't automatically pick up the same environment you do when you log in, though. You need to set that up; either by running your bash profile as part of the cron command or in the script, or by explicitly setting variables in the script (PATH, ORACLE_HOME, etc.). Don't think it's relevant, but redirecting output and spooling to the same file looks like a mistake. Also I would think you'd be emailed the stderr by cron - maybe that needs to be configured somewhere though, or doesn't happen because your overall script exits successfully. – Alex Poole Oct 25 '16 at 16:34
  • @Alex, I only suggested the redirection as a diagnostic effort, not as a long term thing to do. I agree that the environment seems to be the likely culprit here, and since an empty logfile was created my thought was that sqlplus would likely generate some error messages. Keep in mind that sqlplus can give diagnostic output and still return success to the shell, in which case cron would have no reason to think it needed to send an email. – DCookie Oct 25 '16 at 19:49
  • @DCoo,ie - yes I agree the redirection is a good idea anyway,andI often do that from within cron . I'm not sure here, if sqlplus runs, whether the log file will contain the spooled output, the redirected termout (and stderr), or some mix. (That was the 'mistake' I meant, I wasn't referring to your stderr modification). It doesn't seem to be getting far enough for that to matter at the moment... – Alex Poole Oct 25 '16 at 20:07
  • 1
    Yes, I'm fine with what stdout and stderr do, but it''s adding in the spool to the same file that I'm not sure about. I may try that tomorrow to see what happens, if I remember... – Alex Poole Oct 25 '16 at 20:27
  • Probably moot, since there's nothing in the logfile anyway. I just tried it on a windows box and got an SP2-0606 error, "Cannot create SPOOL file "logfile.txt" on the spool command. Stands to reason, it's already been opened by the redirect. I did get all the output I expected, though. – DCookie Oct 25 '16 at 20:50
  • Hi @AlexPoole and @DCookie. Thanks a bunch for your input. It's after I added `>$logfile` on `sqlplus / as sysdba <$logfile` that spool could expand the logfile. I was having difficulty passing a variable to sqlplus in a shell script. When I did that, then spool created the log file successfully. The script is working well now. – Fokwa Best Oct 26 '16 at 08:36

2 Answers2

3

You might want to redirect stderr to stdout, or capture it in another file:

sqlplus / "as sysdba" <<EOF >$logfile 2>&1

If sqlplus is reporting an error you may not be seeing it since you're only capturing stdout to the logfile.

DCookie
  • 42,630
  • 11
  • 83
  • 92
  • +1 for your contribution. Doing what you said allowed me realise that sqlplus command was not found. Thanks a bunch. – Fokwa Best Oct 26 '16 at 08:54
1

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.

Fokwa Best
  • 3,322
  • 6
  • 36
  • 51
  • 1
    If running the script from your shell, the chances are you just had to source the .bashrc / .bash_profile and not declare these explicitly. I'd imagine hard coding the oracle homes like this would be a pain / easily overlooked. – bob dylan Oct 26 '16 at 12:42
  • Thanks @bob. Would try your approach. – Fokwa Best Oct 27 '16 at 12:16