Shell Programming and Scripting

user=<username>
pass=<password>
 
sqlplus /nolog <<EOF
whenever sqlerror exit 99
connect $user/$pass
whenever sqlerror exit 98  -- change this so only the connect attempt uses '99' as the error code
-- do your normal sql stuff now
exit -- assuming a good login and no other error you log out normally
EOF
 
echo $? # will be 99 if bad login attempt, 98 if other sql error happened, or 0 if everything was fine.

sqlplus $user_name/$password@$tns > c:/tmp/check_conn.txt

#!/bin/sh

res=`sqlplus $user_name/$password@$tns <<ENDOFSQL
     set feed off
     set head off
     set pages 0
     set verify off
     select 'ok' from dual;
     exit
ENDOFSQL`

if [ "$res" == "ok"]
then
    echo "SUCCESS"
else
    echo "FAIL"
fi

connStatus=`echo -e "WHENEVER SQLERROR EXIT 1\n SET HEAD OFF\n SET FEEDBACK OFF\n SELECT 'OK' FROM DUAL;\nEXIT" | sqlplus -S -L $user_name/$password@$tns`
sqlPlusRetCode=${?}

if [ "${connStatus}" != "OK" -o ${sqlPlusRetCode} -ne 0 ]
then
	echo "ERROR: Database connection not available."
	# exit 1
else
	echo "INFO: Success."
fi