Check SQL through UNIX shell script

Shell Programming and Scripting
1

I use below command to log in to oracle database

 sqlplus username/password

When i enter into sql prompt i press

quit

If there are any errors it will show just below the output of

 sqlplus username/password

. The errors generally start with keyword

ORA

can we do this by using a unix shell script.? If there are any errors while logging in then script should say " ERROR in database connectivity. "

2

Traditionaly we all seem to favor:

sqlplus -silent username/password <<END
start yourSQLscript
exit;
END

I put username/passwd in a variable to be loaded from a readonly file by the user of the script... (security ...)

3

Might I suggest you don't get into the habit of:-

sqlplus username/password

If someone runs ps -ef | grep sql your credentials will be there to see. It's better to:-

sqlplus -s <<EOSQL
username/password
select count from dual ;
exit ;
EOSQL

... and your details are hidden.

Robin

4

Thanks for your reply.
I just need to check whether i am able to log in or not.
It should log in without any error messages which start with "ORA" / "ERROR"
So script should check these keywords in login message.

5 
sqlplus -s <<EOSQL | tee /tmp/messages.$$
username/password
select count from dual ;
exit ;
EOSQL

echo "You have `egrep -c "^ORA|ERROR" /tmp/messages.$$` issues reported."