I am trying to store sqlplus value to a variable in Linux.
Script is working fine when triggered manually. But value in variable is not stored when the script is called from crontab.
Code:
#!/bin/ksh
~/.profile
v=`sqlplus -s <<!
<<DB details>>
select sysdate from dual;
!
`
echo "$v"
Running sqlplus from a crontab entry can be frustrating. You get a very sparse PATH variable as the shell that crond forks off does not read the "rc" file.
In your ".profile" file do you set ORACLE_HOME? Do you include $ORACLE_HOME/bin in the PATH, and export ORACLE_HOME and PATH? Check that first. Also, I have to note that your code shows the ksh in question executing not sourcing the ~/.profile file. Your script should "source" that file. I also seem to have a superstition about setting and exporting TNS_ADMIN, like this:
export TNS_ADMIN=$ORACLE_HOME/network/admin
You can include database user ID and password in the "here document" by using the /nolog option of sqlplus to avoid flashing those details to anyone who runs ps:
sqlplus -s /nolog 2>&1 << END_ZERO_ROW_CHECK
connect $USER_NAME/$PASSWORD@$SID_INSTANCE
whenever oserror exit failure
whenever sqlerror exit failure
...
END_ZERO_ROW_CHECK
~has the same meaning from CRON and from your terminal? Are both running as the same user?