SQLPlus Connection Issue when using bash script

January 22, 2023

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/