0

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 :)

Improve this question
2
  • 1
    First remove the -S option to get a better response and error message. In general the syntax look OK.
    – 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
    Commented Jan 23, 2023 at 10:18

1 Answer 1

Reset to default
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/

Improve this answer
0

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