Database Connection test in unix Script

Shell Programming and Scripting
1

i have a unix script that gives me the sysdate from the database EDNAMID.WORLD.What i want my script to do the following

1) Establish a database connection
2) if database connection is successfull then echo the message "Connected"
3) put the o/p of the Sql query in a spool file
4) then disconnect from the database by echoing the message "disconnected"

Below is the script that i have written :

#!/bin/ksh
sqlplus -silent Aix/full@ednamid.world @"naveed.sql"<<END
END

code in the sql file naveed.sql

set pagesize 1000 line 1000 feedback off verify off heading off echo off
spool /ednadtu3/u01/pipe/naveed/test/database/d1.txt
select sysdate from dual;
spool off;
exit;

what i want is how to implement the steps 2 and 4 and in which code

2

What I did previously was to verify the connection status only after the connection. For your reference as follow (ksh):

LOGIN_STATUS=`sqlplus -s /nolog <<SQLEND
connect ${UserName}/${PassWord}
set pagesize 1000 line 1000 feedback off verify off heading off echo off
spool /ednadtu3/u01/pipe/naveed/test/database/d1.txt
select sysdate from dual;
spool off;
exit;
SQLEND`

echo ${LOGIN_STATUS} |grep "ORA-" >> /dev/null

LOGIN_STATUS=$?

if (( $LOGIN_STATUS ))
then
     echo "Connected"
else
     echo "Problem connecting"
fi
3

Where should i write this code inside my unix script or sql file ? Also where do i put the database name ednamid.world

4

That would be an entire shell script handling all the way, including what the sql file is doing.

However you need to ensure your oracle environment settings are all set