check the DB connection through Unix

Shell Programming and Scripting
1

Hi all,

I have written the script to check the connectivity of the Database ,the scripts show me the correct error when i try to give the wrong credential's but it also show Connected and trigger's the child jobs which that should not happen , whenever the connection is not established properly it should exit from the script, which is not happening in my script..please look into the script below and let me know the changes please
-------------------------------------------
Script
---------------------------------------------

#!/usr/bin/bash
. /etc/profile.local
. /usr/local/lib/job_functions
. /infa_shared/infadev_d2/ShellScripts/xxx/bin/set_env
export foldername='m'
export wfname='s'
flag='N' #value used to test if DB connection is sucessfull, default is no
uid=bbb #userid for the Database
pwd=111 #password for Database
hostname=yyy #Hostname of the Database
while [ ! -f /user/shiv/flag.trg ] #while loop,loop untill flag is set to Y
do
  sqlplus -s /nolog <<ENDSQL
  connect bbb/111@yyy
  WHENEVER SQLERROR EXIT SQL.SQLCODE
  WHENEVER OSERROR EXIT 10
  EXIT;
  ENDSQL
  if [ $? -eq 0 ]
  then
    echo 'connected OK'
    . /infa_shared/infadev_d2/ShellScripts/xxx/bin/execute_workflow $foldername $wfname
    touch /user/shiv/flag.trg #Set the flag to Y to exit the loop as the workflow is complete
    chmod 777 /user/shiv/flag.trg
  else
    echo 'connection FAIL'
    sleep 10 #Sleep for 10 secs and again loop through
  fi
done
rm /user/shiv/flag.trg

-----------------------------------------------------------------
output of the script
------------------------------------------------------------

-bash-3.00$ DB_Connection
ERROR:
ORA-01017: invalid username/password; logon denied

connected OK
-bash-3.00$

Thanks in Advance

2

One correction:

while [ ! -f /user/shiv/flag.trg ] #while loop,loop untill flag is set to Y
do
  sqlplus -s /nolog <<ENDSQL
  connect bbb/111@yyy
  WHENEVER SQLERROR EXIT SQL.SQLCODE
  WHENEVER OSERROR EXIT 10
  EXIT;
ENDSQL

ENDSQL has to be in the leftmost column.

Why can you not use tnsping?

3

The WHENEVER directives get activated after you connect. Set them before you try to connect:

...
while [ ! -f /user/shiv/flag.trg ] #while loop,loop untill flag is set to Y
do
  sqlplus -s /nolog <<ENDSQL
  WHENEVER SQLERROR EXIT SQL.SQLCODE
  WHENEVER OSERROR EXIT 10
  connect bbb/111@yyy
  EXIT;
ENDSQL
...

@jim: tnsping only tells you if there is a listener accepting connections but not if there's a database to connect to...