Using sqlplus to programmatically check connection

Question

Is it possible to use sqlplus to programmatically check a database connection (in particular, the SYS account)?

I'm thinking something along this line

Run sql plus (using Process.Start) with some parameter, giving the sys username and password
Check the exit code (or even the stdout), whether it is successful or not. The best way I can come up so far is to check the stdout string whether the substring "ERROR" exist, but I don't think this is very clean

Is this something possible? What parameter do you suggest I use? The reason I want to do this is because System.Data.OracleClient doesn't support connection as SYSDBA

Andrew Skirrow · Accepted Answer · 2013-07-05T11:18:08.853

2

This might work

whenever sqlerror exit failure
whenever oserror exit failure
conn user/pwd@db-name
select 'success' from dual;
exit

The above script will have the following behaviour:

sqlplus process will return non-zero code if there was an error
Otherwise sqlplus will return zero

Note that I haven't tested the above.

Amendment

The script should be run as follows on unix:

sqlplus -nolog @script-file.sql

And as follows on windows:

sqlplus /nolog @script-file.sql

edited Jul 05 '13 at 11:18

answered Feb 01 '12 at 09:19

Andrew Skirrow

3,402
18
41

this doesn't exit on failure.. Maybe the error doesn't classify as sqlerror or oserrror? – Louis Rhys Feb 01 '12 at 10:30
@Louis How are you running the above script? See amended section of post. – Andrew Skirrow Feb 01 '12 at 11:49
Fyi, I had to use "/nolog" instead of "-nolog" for 'SQL*Plus: Release 11.2.0.3.0 Production' (Win32). e.g. sqlplus /NOLOG @test.sql – Nick Grealy Jul 04 '13 at 22:43

score 1 · Answer 2 · answered Feb 01 '12 at 12:01

You don't need to get a session to check that the connection is running.

If you try to access the database using invalid credentials you'll get a ORA-01017 error. If the connection is not available or the listener is running but the database is not mounted (i.e. it can't check the credentials even though it can connect) you'll get a different error code.

See also the Nagios check_oracle_v2 script for an example.

score 0 · Answer 3 · answered Jun 06 '17 at 07:22

I had similar question. I discovered we could specify what failure code could be sent depending on the case:

sqlplus /nolog << EOF
 WHENEVER SQLERROR EXIT 50
 WHENEVER OSERROR EXIT 66
 connect /@${MISTERY_DB}
 exit;
EOF

Then to check the return code:

RET_CODE=$?
if [ ${RET_CODE} -eq 50 ] ; then
  echo "it was an SQLERROR"
fi

Using sqlplus to programmatically check connection

3 Answers3