SQLPlus Connection Issue when using bash script

Question

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 MyUsername/MyPassword@MyHostname:1521/MyServiceName

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

Thanks in advance :)

First remove the -S option to get a better response and error message. In general the syntax look OK. — Wernfried Domscheit
– Wernfried Domscheit, Commented Jan 22, 2023 at 19:14
You have a missing closing backquote when calculating V1. Note also that command substitution using backquotes is deprecated. Also I don't understand why the question is tagged bash; how are you invoking this script? — user1934428
– user1934428, Commented Jan 23, 2023 at 10:18

Eric Marceau · Accepted Answer · 2023-01-22 19:17:03Z

2

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/

edited Jan 22, 2023 at 19:17

answered Jan 22, 2023 at 19:13

Eric Marceau

1,7571 gold badge11 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQLPlus Connection Issue when using bash script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related