Dear Folks,
I have written a shell script which internally connects to oracle database through sqplplus command line.
The script runs fine when run manually.
Now I am scheduling it to run (Linux environment) from crontab.
In crontab it is failing with an error:
sqlplus command not found
I have exported ORACLE_HOME path from inside the script, but still its failing.
Help!!!
Can you show the entire command line from crontab?
Also, the script?
Hi Joey,
The command line from crontab: (was running it yesterday)
44 16 19 02 * /infinys/infroot/STCTEST12/TESTING_IRB/translation_space_find.sh
The script (translation_space_find.sh):
export ORACLE_HOME='/oracle/app/oracle/product/11.2.0.client'
export DATABASE='stc_irb_usr/stcirbusr_stctest12@stctest12'
export FGRED="\033[31m"
export FGGREEN="\033[32m"
export BOLD="\033[1m"
export NORMAL="\033[m"
echo "Started counting number of records with trailing/leading spaces..."
#For counting trailing spaces block
echo "\
set pagesize 0
select count(*) from itemtranslation
where regexp_like(translated_item, '^[ ]+.*');" |
sqlplus -s $DATABASE | read trail
echo -e $FGGREEN $BOLD "Number of ITEMTRANSLATION records with trailing spaces is:" $NORMAL
echo -e $FGRED $BOLD "$trail" $NORMAL
echo "\
set pagesize 0
select count(*) from itemtranslation where regexp_like(translated_item, '.*[ ]+$');" |
sqlplus -s $DATABASE | read lead
echo -e $FGGREEN $BOLD "Number of ITEMTRANSLATION records with leading spaces is:" $NORMAL
echo -e $FGRED $BOLD "$lead" $NORMAL
Hi,
Use the below export after export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH
Hi MKR,
Thanks. Error got resolved after export PATH=$ORACLE_HOME/bin:$PATH
However, below issues persist:
1) Values retrieved from database are not being printed. (as checked in mail generated in /var/spool/mail).
i.e. Number of ITEMTRANSLATION records with trailing spaces is:
After this no values are printed, which means that the count is not retrieved from database.
2) Deeper in the script I have used some if blocks, eg.
if [ $v_count_1 = 0 ]
then
echo "something1"
elif [ $v_count_1 -gt 0 ]
then
echo "something2"
For these, the cron is throwing errors:
line 69: [: =: unary operator expected
line 93: [: -gt: unary operator expected
Could you please help resolve these also 
Here is a example
change
if [ $v_count_1 = 0 ]
to
if [ "$v_count_1" = 0 ]
#!/bin/sh
v_count_1=0
if [ "$v_count_1" = 0 ]
then
echo "something1"
else
echo "vicky"
fi
Ahem... sorry, but both are wrong. If you want to compare strings you have to write:
if [ "$v_count_1" == "0" ]
Notice not only the quotes around BOTH values but also the double equal signs. It is like in C: single "=" is an assignment, double "==" is for comparison.
If you want to compare integer values use "-eq" instead:
if [ $v_count_1 -eq 0 ]
other integer comparisons are:
-eq (equal to)
-ne (not equal)
-lt (lower than)
-le (lower or equal)
-gt (greater than)
-ge (greater or equal)
@tamojitc:
You are probably falling for "Cron Problem Number One", parts of which you have by now corrected by setting the path. There is probably a file ".ora_env" or something such in the HOME-directory of the oracle-user. I suggest sourcing in this at the beginning of the script to set the rest of the oarcle-environment (instance name, etc.):
#! /bin/ksh
. ./path/to/oracle-users/home/.ora_env
<rest of the script>
I hope this helps.
bakunin