Connect to oracle database from shell script

Question

I am trying to connect to an oracle database from a shell script . Script for connecting to database is given below:

#!/bin/bash
# Shell script to run sql files from command line.
# Pre-Req: sqlplus client shall be installed already.
###########################################################
# Variables Section (DB Details)
###########################################################
driverClassName=oracle.jdbc.driver.OracleDriver
url="(description=(address_list=(address=(protocol=TCP)(host=oradb.test.env.org)(port=1521)))(connect_data=(service_name=TEST_S)))"
DB_UserName="abc"
DB_Password="abc"
LogDirectory="/var/tmp/logs"
DataDirectory="/var/tmp/data"
DB_HostName="oradb.test.env.org"
DB_Port="1521"
DB_SID="KONTOR"
DIR_SqlFiles="C:\git\sql"
##########################################################
# All Script Functions Goes Here
##########################################################


db_statuscheck() {
  echo "`date` :Checking DB connectivity...";
  echo "`date` :Trying to connect "${DB_UserName}"/"${DB_Password}"@"${DB_SID}" ..."
  echo "exit" | sqlplus -S ${DB_UserName}/${DB_Password}@${url} | grep -q "Connected to:" > /dev/null
  if [ $? -eq 0 ]
  then
     DB_STATUS="UP"
     export DB_STATUS
     echo "`date` :Status: ${DB_STATUS}. Able to Connect..."
  else
     DB_STATUS="DOWN"
     export DB_STATUS
     echo "`date` :Status: DOWN . Not able to Connect."
     echo "`date` :Not able to connect to database with Username:  "${DB_UserName}" Password: "${DB_Password}" DB HostName: "${DB_HostName}" DB   Port: "${DB_Port}" SID: "${DB_SID}"."
     echo "`date` :Exiting Script Run..."
     exit
     fi
 }

Main() {
   echo "`date` :Starting Sql auto run script."
   db_statuscheck
   echo "`date` :Sql auto run script execution completed."
}

Main | tee autosql.log

When I tryto connect directly from terminal connection string works, but from shell script it fails. Output I am getting is:

 7. apr 2016 15:18:09 :Starting Sql auto run script.
 7. apr 2016 15:18:09 :Checking DB connectivity...
 7. apr 2016 15:18:09 :Trying to connect abc/abc@TEST_S ...
 7. apr 2016 15:18:09 :Status: DOWN . Not able to Connect.
 7. apr 2016 15:18:09 :Not able to connect to database with Username: abc  abc Password: kjopsprosesser_utv4 DB HostName: oradb.test.env.orgDB Port: 1521 SID: TEST_S
 7. apr 2016 15:18:09 :Exiting Script Run...

Alex Poole · Accepted Answer · 2016-04-07 14:06:18Z

8

It doesn't help that you are squashing all the interesting output from SQL*Plus. But in this case you're shooting yourself in the foot a bit.

You're using the -s silent flag, which means SQL*Plus doesn't display the banners, and - crucially - doesn't display the Connected to: message. Your grep is never going to find anything.

You can check that by running it with that flag from a shell prompt; you'll see something like:

$ echo "exit" | sqlplus -S abc/abc@TEST_S
$

It will just return to the shell prompt, with no output.

So if you remove that flag the banners and message will be displayed and you can check for the message.

I'd suggest you capture the entire output in a variable (or file), grep to check for successful connection, and if you think there's a problem display the output so you can see what's actually wrong. If you have a genuine problem you currently have no way to tell what is actually wrong.

answered Apr 7, 2016 at 14:06

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

2 Comments

Zoe Over a year ago

I removed grep and -S parameter. Redirected output to file and added grep to check for successful connection and it worked. Not sure exactly who did it cause script to work since I didn't change anything in connection string. Original error I've been getting was related to wrong username/password, which is weird since it didn't have nothing to do with it. Anyway I am marking this answer as the correct one. Thanks :)

Alex Poole Over a year ago

It may have been connecting successfully before, but the -s meant you didn't see the Connected to message even if it worked, so the grep failed.

Christopher Littlewood · Accepted Answer · 2019-11-21 04:50:21Z

1

ORACLE_HOME=PATH_TO_ORACLE

export ORACLE_HOME

PATH=$PATH:$ORACLE_HOME/bin

export PATH

sqlplus -s ${DBUSER}/${DBUSERPASSWORD}@//${HOST}:${PORT}/${SERVICE_NAME} <<EOF
set linesize 32767
set feedback off
set heading off

select * FROM dual;

EOF

edited Nov 21, 2019 at 4:50

Christopher Littlewood

8032 gold badges14 silver badges38 bronze badges

answered Sep 9, 2019 at 12:39

Amita Rawat

1731 gold badge2 silver badges6 bronze badges

Collectives™ on Stack Overflow

Connect to oracle database from shell script

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related