Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Connect to oracle database from shell script

Tags:

shell

oracle

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...
like image 992
Sanja Avatar asked Apr 07 '16 13:04

Sanja


People also ask

How do you connect to a database in UNIX shell script?

The first thing you have to do to connect to oracle database in unix machine is to install oracle database drivers on the unix box. Once you installed, test whether you are able to connect to the database from command prompt or not. If you are able to connect to the database, then everything is going fine.

How will you write a shell script to connect to SQL database?

Use the standard command-line tool. Just about all the databases you'd use have a command-line tool: sqlplus for Oracle, mysql for MySQL, psql for PostgreSQL, etc. Generate the SQL in your shell script, either into a file (or from an external file) or using shell redirection from standard out.


1 Answers

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.

like image 159
Alex Poole Avatar answered Oct 19 '22 12:10

Alex Poole