SQLPlus Connection Issue when using bash script

Trying to connect to ORACLE SQLPLUS using unix shell script. But it is getting failed..
Looks like the script in line 3 is incorrect as I am passing username, password and SID

#!/bin/sh
cd /dev/shrd/alt/test1/stest/ptest
V1=`sqlplus testuser/passwd@testSID <<EOF
SELECT count(*) FROM test_table WHERE region='Aus';
EXIT;
EOF`
if [ -z "$V1" ]; then
  echo "No rows returned"
  exit 0
else
  echo $V1
fi

I got an error stating -ORA-12162: TNS:net service name is incorrectly specified when I added – sqlplus $username/$password in the script.

Can anyone please confirm if the below syntax is valid and I can add it in shell script?

**sqlplus USERNAME/PASSWORD@SID**

Kindly guide me if I’m missing something (like Hostname, Port Number,TNS_entry or something else).

Thanks in advance 🙂

>Solution :

Until you are successful in obtaining any output from your sqlplus command, you should not use "-S". Without that, sqlplus will provide you with much-needed error-reporting/feedback to debug your command interface/call.

Also, as per this, it is inadvisable to provide the password on that command line. For that reason, the service/DB administrators probably disallow that form of accessing the service/DB/

Leave a Reply