Sqlplus command is not working via plink

Question

I have a shell script in which I am using sqlplus command to fetch the data from database (Installed on Linux). same script is working fine on Linux environment. When I execute the script on window environment via using below batch file.

set ORACLE_TERM=xterm
set ORACLE_BASE=/home/pwcadm/app/pwcadm
set ORACLE_HOME=/home/pwcadm/app/pwcadm/product/11.2.0/client_1
set ORACLE_HOSTNAME=kyora02.kymab.local
set ORACLE_SID=orcl
set ORA_NLS11=$ORACLE_HOME/nls/data
set LANG=en_US.UTF-8
set PATH=/opt/CollabNet_Subversion/bin:/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin:/home/pwcadm/app/pwcadm/product/11.2.0/client_1/bin:/home/pwcadm/app/pwcadm/product/11.2.0/client_1/bin:/home/pwcadm/app/pwcadm/product/11.2.0/client_1/lib/site_perl/5.8.3/i686-linux-thread-multi:/home/pwcadm/app/pwcadm/product/11.2.0/client_1/perl/lib/5.10.0/x86_64-linux-thread-multi:/home/pwcadm/app/pwcadm/product/11.2.0/client_1/perl/bin:/home/pwcadm/Informatica/PowerCenter9.1.0.3/server/bin
set LD_LIBRARY_PATH=/home/pwcadm/app/pwcadm/product/11.2.0/client_1/lib:/home/pwcadm/app/pwcadm/product/11.2.0/client_1/lib32:/home/pwcadm/Informatica/PowerCenter9.1.0.3/server/bin
cd C:\Program Files\PuTTY
plink csaadm@172.16.122.11 -pw csaadm /app/csa/REG_AUTOMATION/scripts/CHECK_GAN_INSERT.sh

Batch file is prompting the below error message. Even I have set all the paths.

C:\Program Files\PuTTY>plink csaadm@172.16.122.11 -pw csaadm /app/csa/REG_AUTOMA
TION/scripts/CHECK_GAN_INSERT.sh
Error 6 initializing SQL*Plus
SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory
FAIL
/app/csa/REG_AUTOMATION/scripts/CHECK_GAN_INSERT.sh: line 27: [: -ne: unary oper
ator expected

CHECK_GAN_INSERT.sh

ACCOUNT_GLOBAL_COUNT=$(echo "
        set heading off
        set feedback off
        set verify off
        set trimspool on
        set trimout off
        set pagesize 0
        set space 0
        whenever sqlerror exit 2;
SELECT COUNT(*) FROM ACCOUNT_GLOBAL WHERE NAME='test';
"|/home/pwcadm/app/pwcadm/product/11.2.0/client_1/bin/sqlplus -S ${DB_USERNAME}/${DB_PASSWORD}@${CONNECTING_STRING})

EROR_MSG=$(echo ${ACCOUNT_GLOBAL_COUNT} |grep ORA|wc -l)

if [ ${EROR_MSG} -ne 0 ]
then
        echo "${ACCOUNT_GLOBAL_COUNT}"
        exit;
fi

if [ ${ACCOUNT_GLOBAL_COUNT} -ne 0 ]
then
        echo "PASS"
else
        echo "FAIL"
fi

Please help.

Answer 1

We haven't enough information about your problem, but here is my guess:

• you have to set ORACLE_HOME env. var each time you log in (or add it in auto executable scripts),
• you have to extend PATH too (use export PATH=$PATH:$ORACLE_HOME/bin),
• also, may be you need to start Oracle instance.

Check these three points and update your question if your problem is still not solved.