Running an Oracle sql script via Cron 1

[bryantg]

Hello All,

(Oracle 7 and solaris 2.6)

I am trying to run an sql script. The cron log shows it is started but sql script does not run.

The cron runs as root.

the script works if i initiate it from the command line.

I think it maybe an oracle environment issue... but i am not sure how to check and how to correct it....

help!!!

 

[jimbopalmer]

OK, Several disclaimers:
I run my Oracle scripts in cron as oracle, and my application scripts in cron as applmgr
in cron I start a script wich runs the SQL

59 23 * * * /applmgr/mippy/mthendval.sh >> /applmgr/mippy/mthendval.log

in the .sh I set my values

#!/bin/ksh
. /usr/local/bin/BIG2set
MIPPY_PATH=/applmgr/mippy
/oracle/product/734/bin/sqlplus mippy/mippy @MIPPY_PATH/mthendval.sql I love everyone til they prove otherwise, sadly some prove otherwise SO quickly

 

[ligang1000]

Can U consider to use ProC ?

 

[maxcrook]

Hi I use Oracle 7.3.4 and the script using this link works:

http://www.dbaoncall.net/references/ht_use_crontab.html

Hope it proves useful

Max

 

[silverwingtx]

I had this problem as well. I spoke with my UNIX administrator and he says that cron does not recognize the ENVIRONMENT variables set when a user logs in so they must be declared within the script. You can view a list of them by typing "env" from the UNIX prompt.

 

[rcurva]

You can try using the "su -" in your crontab. For example;

In your crontab;

59 23 * * * /applmgr/mippy/mthendval.sh >> /applmgr/mippy/mthendval.log

In your mthendval.sh script;

su - oracle "export ORACLE_SID=mysid; sqlplus -s $USER/$PSWD @mysql.sql > mysql.log "

This way, the oracle user environment will be applied as if he was the one who logged in and run the script.




Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[nwardez]

Hi,

Some cron daemon (depending on the Unix) accept environment variables in the beginning of user's cron table (Solaris can deal with MALTO, SHELL, LOGNAME, USER and HOME). For example:

MAILTO="webmaster@mycompany.com"

Do you use the right OS ? ;-)))))))