Connect to oracle database from shell script

Connect to oracle database from shell script

提问by Sanja

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

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:

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...

回答by Alex Poole

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.

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 -ssilent flag, which means SQL*Plus doesn't display the banners, and - crucially - doesn't display the Connected to:message. Your grepis never going to find anything.

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

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

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.

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.

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.

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.

回答by Amita Rawat

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