How to check if the connection to the database was successful?

Question

I need to check if the database connections are successful or not. Using command

sqlplus <username>/<password>@<SID>

If the connection is successful than the command returns a variable flagged as Y/N or whatever. This should be done for multiple servers and the flags stored in a file.

I am not sure how to conitnue?

possible duplicate of [Check database connectivity using Shell script](http://stackoverflow.com/questions/3779569/check-database-connectivity-using-shell-script) — Maheswaran Ravisankar, Jun 23 '14 at 06:39
Similar to that, but how do I store multiple username, pass etc... and automate the script for multiple dbases? — Arcane, Jun 23 '14 at 06:43

score 1 · Accepted Answer · answered Jun 23 '14 at 07:21

Linux Platform

TWO_TASK=TNS_ALIAS

sqlplus -s /nolog <<!
whenever sqlerror exit 1;
connect user/pass;
select * from dual;
exit
!

if [ $? = 0 ]; then
    echo "success"
else
    echo "failed"
fi

Windows Platform

set local=TNS_ALIAS
@(
   echo whenever sqlerror exit 1^; 
   echo connect user/pass;
   echo select * from dual^;
   echo exit^;
) > %TEMP%\run.sql
sqlplus /nolog @%TEMP%\run.sql

if "%errorlevel%"=="0" cls &echo success.
if "%errorlevel%"=="1" cls &echo failed

How to check if the connection to the database was successful?

1 Answers1