to establish database connection using UNIX shell script

Question

I am new to shell scripting and have less or no idea on this. I have to read a db.properties file which has the database connection details i.e. to which db to connect. Then i have to establish a connection to that database and perform an operation to check the current time.

Below is my db.properties file :-

driverClassName=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@171.01.187.94:1532:DEV
userName=abc
password=abc

Below is my script to call the db.properties file :-

#!/bin/bash

file="./database.properties"

if [ -f "$file" ]
then
    echo "$file found."
 . $file

  echo "User Id       = " ${userName}
  echo "user password = " ${password}
  echo "url  = " ${url}

  sqlplus -S ${userName}/${password}@${url}

else
    echo "$file not found."
fi

But i am getting the below error :-

ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

Could anyone please help on the above issue ?

Your local TNS setup for Oracle doesn't include the database you want to connect to. You include a Java JDBC driver class name, but you use sqlplus client. Seems off to me. — duffymo
– duffymo, Commented Aug 11, 2015 at 12:24
@duffymo :- yes it is not :( could you please help me modify the script as per my requirement ? — Neethu Shaji
– Neethu Shaji, Commented Aug 11, 2015 at 12:25
You don't pass a JDBC URL to SQL*Plus. That's never going to work. You'd generally want to create an entry in your tnsnames.ora file, assuming that you are using local naming, that points to this database. Assuming that TNS alias is named "dev", you'd then sqlplus user/password@dev to connect to the database. — Justin Cave
– Justin Cave, Commented Aug 11, 2015 at 12:27
See the Answer in stackoverflow.com/questions/23534668/… for more (but incomplete) info on tnsnames.ora. Good luck. — shellter
– shellter, Commented Aug 11, 2015 at 12:29

Daniel Vukasovich · Accepted Answer · 2015-08-11 23:39:56Z

5

Don't worry about tnsnames.ora definition, you have all information needed to establish a connection using sqlnet.

Modify your database.properties file as follows:

driverClassName=oracle.jdbc.driver.OracleDriver
url='(description=(address_list=(address=(protocol=TCP)(host=171.01.187.94)(port=1532)))(connect_data=(service_name=DEV)))'
userName=abc
password=abc

And that's it. You don't need to change your script.

#!/bin/bash

file="./database.properties"

if [ -f "$file" ]
then
    echo "$file found."
 . $file

  echo "User Id       = " ${userName}
  echo "user password = " ${password}
  echo "url  = " ${url}

sqlplus -S ${userName}/${password}@${url}

else
    echo "$file not found."
fi

Note: I assumed that DEV is the database service name, if it's the database SID just modify configuration string as:

driverClassName=oracle.jdbc.driver.OracleDriver
url='(description=(address_list=(address=(protocol=TCP)(host=171.01.187.94)(port=1532)))(connect_data=(sid=DEV)))'
userName=abc
password=abc

Regards

answered Aug 11, 2015 at 23:39

Daniel Vukasovich

1,7421 gold badge18 silver badges26 bronze badges

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

1 Comment

Neethu Shaji Over a year ago

I have added the below in my script #!/bin/bash file="./database.properties" query="SELECT SYSDATE -2 FROM DUAL" if [ -f "$file" ] then echo "$file found." . $file echo "User Id = " ${userName} echo "user password = " ${password} echo "url = " ${url} sqlplus ${userName}/${password}@${url} <<EOFSQL $query; EOFSQL else echo "$file not found." fi

user9491210 · Accepted Answer · 2018-03-14 11:47:31Z

0

please try / in between port and SID name. username/password@host:port/SID

answered Mar 14, 2018 at 11:47

user9491210

1

Collectives™ on Stack Overflow

to establish database connection using UNIX shell script

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related