connecting to a database through a script...if not give an error msg


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers connecting to a database through a script...if not give an error msg
# 1  
connecting to a database through a script...if not give an error msg

Hello ,
i have a problem in writing the shell script..
i have a script already written n just need to make a change in which when the script is not able to connect to the database ..it should print an error msg on the screen.

I have already seen previous posts on how to connect to the database ..but i didnt find any in which it prints out an error msg when not connected to the database... I am posting my script below.

I have tried it this way ..but it doesnt seem to work.. Somewhere i guess it is been written in a wrong way.

if[!$STOCK/sqlplus VKS#1/#vokus02@$VOKUS @range.sql]
print "Cannot connect to the Database";


thank u in advance
...................................................................................................
#!/bin/ksh
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/export/home/oracle/app/oracle/product/8.1.7/lib
export LD_LIBRARY_PATH

export ORACLE_HOME=/export/home/oracle/product/10.1.0/client_1

export STOCK=/export/home/oracle/product/10.1.0/client_1/bin


export ENT=/home/thomas/ent/uc2



VOKUS=**********
cd $ENT


# $STOCK/sqlplus VKS#1/#vokus02@$VOKUS @range.sql
if[!$STOCK/sqlplus VKS#1/#vokus02@$VOKUS @range.sql]
print "Cannot connect to the Database";
# 2  
Try this LINK.
# 3  
hello ,
thank u for the quick reply...i had a few more questions regarding the script which u sent me

please may i know wht does it mean by exit 140 ? is it a timeout of 140sec?

Would the script also work this way ?

exit 140
if [ "$STOCK/sqlplus VKS#1/#vokus02@$VOKUS @range.sql" -ne 140 ]
then
echo "Oracle is down!"
fi

because if the script connects itself to the database then the sql query should be executed .Only when it does not connect it should display that "oracle is down".



thank u in advance
# 4  
No, it's not a timeout. It's only a "custom" exit code, you can use whatever you want. Now I don't remember the detail, but the only important thing is to use an exit status (for a successful connection) different from 0, because sometimes sqlplus exits with 0 also when the connection is not possible due to an incorrect connect string or database down.

The code you writed is incorrect and it doesn't work. You should try this:

Code:
USER="VKS#1"
PASS="#vokus02"
LOGFILE="/tmp/range.log"

${STOCK}/sqlplus -s ${USER}/${PASS}@${VOKUS} <<EOF >${LOGFILE} 2>&1
whenever oserror exit 98;
whenever sqlerror exit 99;
@range.sql
exit 140;
EOF

if [ "$?" -ne 140 ]
then
   echo "Connection error (Oracle down?)"
else
   echo "Connection estabilished, script executed."
fi

Notice that I've redirected the output to a log file instead of "/dev/null", otherwise you can't track the output of the sql script (you will only get the message error/estabilished at the end).
If you want more details you can remove the silent option (-s). If you don't want to see anything except the final message, change the LOGFILE variable to "/dev/null".

You can also perform two sqlplus connection: the first executes my original script for testing if Oracle is available and then (only if the db is up) a second connection will execute the sql script.

It all depends on what you want to do Smilie
 

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #326
Difficulty: Medium
Time on Computers is commonly referred to as GNU Time.
True or False?

10 More Discussions You Might Find Interesting

1. Solaris

Connecting to Oracle database

Hi guys, long time no typing :D I have problems when I try to connect to Oracle database from SQL Developer which is located on my Windows box. Server is running Solaris 11/11 and Oracle 11 enterprise. I can log in locally with sys account to database, start it up and shut it down but when I... (2 Replies)
Discussion started by: solaris_user
2 Replies

2. Shell Programming and Scripting

script for connecting database in loops

Hi experts. I have different database and schema and their passwords in one file. In Unix : I want to create the script that connect the sqlplus and execute the select query one by one schema with different database. Ex: password File Format databse schemaname password DB1 name1 ... (1 Reply)
Discussion started by: abhigrkist
1 Replies

3. Shell Programming and Scripting

Connecting database

Hi all, In my concern they gave a task to me do cran job like To write a shell script for To connect a database it should execute a query it should save the output of the query after getting the output it should send a mail to you inbox for every one hour I can able to... (8 Replies)
Discussion started by: thelakbe
8 Replies

4. Shell Programming and Scripting

connecting to different database in sqr

Hi, My requirement is to generate a single text output which fetches data from two different databases. I have two different sqrs to generate text file1(data from db1) and file2(data from db2). can I execute both the sqrs from a single shell script? If yes, pls let me know how to connect to db2... (1 Reply)
Discussion started by: vimalr
1 Replies

5. Shell Programming and Scripting

Connecting to Database

Hi, I want to write a shell script which connects to a Database, executes a set of SQLs and displays a message for every SQL that was run whether any records have been returned or not? Can anyone help me in this regard? Appreciate your help and concern. Thanks, Dave (1 Reply)
Discussion started by: yoursdavinder
1 Replies

6. Shell Programming and Scripting

Connecting to oracle database from shell script

Hi all, I am satyakiran , i am new to the forum. i never done shell scripts for connecting to the data base (oracle) and fetching the data from the database( thru sql select statements ) i want to know 1. how to connect to the data base(oracle) using shell script 2. how to retrieve data... (8 Replies)
Discussion started by: satyakiran
8 Replies

7. Windows & DOS: Issues & Discussions

Client is not connecting to the Database

Hi, Am running my client in Windows and the database (IBM DB2) is in AIX machine. if I open my client its not connecting to the database at first time and failing in a particular SQL Query. But if I close the client and open again then its connecting and the query is also working fine. Don't... (0 Replies)
Discussion started by: mvictorvijayan
0 Replies

8. Shell Programming and Scripting

Connecting and Querying DB2 database from Unix Script?

I am trying to develop a script that can connect to a DB2 database mainframe and Query the database and display the results. I've been researching, but I have yet to find a definitive solution where I can enter in the Mainframe DB2 address db2:// and query the database and return the results. If... (2 Replies)
Discussion started by: developncode
2 Replies

9. Shell Programming and Scripting

error connecting database from perl

Hi, While i am trying to connect to Oracle database from Perl using DBI module,am getting the error as follows : Can't load '/usr/local/fuseperl-modules/lib/i586-linux-thread-multi/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libwtc9.so: cannot open shared object file: No such file... (4 Replies)
Discussion started by: DILEEP410
4 Replies

10. AIX

command to give me Database

Which is command I could used, to give me the kind of database ,size of database and version of database ? (4 Replies)
Discussion started by: magasem
4 Replies

Featured Tech Videos