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.


v=`sqlplus -s <<!
<<DB details>>
select sysdate from dual;
echo "$v"
  • Are you sure ~ has the same meaning from CRON and from your terminal? Are both running as the same user? – John WH Smith Jan 13 '15 at 18:09
  • Script is picked by crontab and it invoked .profile too. but the value for the variable remains blank(unset) – user3377923 Jan 13 '15 at 18:12

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
whenever oserror exit failure
whenever sqlerror exit failure
  • This is almost guaranteed to be your problem. Bruce describes it well. It is largely an environment (PATH, LD_LIBRARY_PATH ) issue combined with making sqlplus behave as you want. – jim mcnamara Jan 13 '15 at 20:47
  • Thanks Bruce, But my .profile indeed contains : PATH=/usr/sbin:$PATH:/sbin:/opt/oracle/product/102010_cl_64/cl/bin:/opt/oracle/product/102010_cl_64/cl/lib:/usr/local/sbin:/opt/oracle/product/102010_cl_64/cl/bin export PATH and ORACLE_HOME=/opt/oracle/product/102010_cl_64/cl export ORACLE_HOME but it is still not setting the variable value. – user3377923 Jan 14 '15 at 11:48
  • @user3377923 - your .profile may indeed contain that, but your script doesn't source .profile, it executes it. put ". " (dot space) in front of "~/.profile" to get ksh to source .profile rather than execute it. – Bruce Ediger Jan 14 '15 at 14:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.