verify that the script works under the cron user id out in the shell normally.
personally, i never like executing a script directly through cron and always
make sure that a specific environment is sourced into the shell running that command.
for example:
1 * * * * /acus/bin/cron_env.sh some-shell.cmd with some arguments
and cron_env.sh looks like:
. /acus/bin/set_env.sh
$*
i'm willing to bet you have environment variable issues. something's not getting set through cron.
verify that the crontab user under this job has connect permissions and read permissions tothe database and tables you're accessing, write permissions also, if necessary.
verify that every directory in your fullpaths are searchable by this user.
what is the error message you're getting from the cron job?
have you compared the environmental variables from the normal login shell
to the cron shell used?
you know these are totally different, right?
for example, i believe ORACLE needs to access certain database information through the oranames.tns file which is stored
under an environmental variable ORACLE_ADMIN_DIR or something like that. . . .
this variable would not be set in your cron environment, unless you explicitly did so.
>>>what is the error message you're getting from the cron job?
I am not getting any error message ..it is just that the cron is overlooking the sqlplus line in the script and executes everything else
>>>for example, i believe ORACLE needs to access certain database information through the oranames.tns file which is stored
under an environmental variable ORACLE_ADMIN_DIR or something like that. . . . this variable would not be set in your cron environment, unless you explicitly did so
If i execute the script from absolute path from command line ..everything works..so i believe all the ORACLE parameters are set in .profile..
Is there anything that needs to be set specifically for cron job to work?
you must explicitly do so within a shell, within your cron command.
try putting everything into another script like this:
quirks_new_script.sh:
. /home/user/.profile
/home/dreg/script.sh
vger's recommendation will only work if there are no "sh" incompatible commands within the .profile.
edit * -- in fact, on my machine, cron does not interpret ~ correctly.
What I'm saying is this:
cron is run under the Bourne shell.
Your script is who-knows-which shell.
Therefore, you must create something that sets up the environment explicitly for your ORACLE script to run.
Do not assume ANYTHING is set up.
Remember: the user's .profile IS NOT USED THROUGH cron.
If a script executes from the command line but has issues in cron, it is possible that your environment is not getting set properly. With Oracle in particular, you need : ORACLE_SID, ORACLE_HOME set in your environment and ORACLE_HOME/bin must be concatenated to your path. This may be done for you by sourcing "oraenv" which typically resides in /usr/local/bin (check with your DBA). If you look in your .profile, it may contain the necessary steps to establish your environment. You can add these environment settings to your script, or you can source a separate file to set them.
an example. If your database SID is "prod1" and Oracle Home is /u01/app/oracle/product/10.2.0 then add the following to your Korne or Bourne shell:
appreciate it.