UNIX for Dummies Questions & Answers

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";

Try this LINK.

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

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