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

score 2 · Accepted Answer · 2013-07-05 11:18:08Z

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 5, 2013 at 11:18

answered Feb 1, 2012 at 9:19

user208567

Sign up to request clarification or add additional context in comments.

3 Comments

Louis Rhys Over a year ago

this doesn't exit on failure.. Maybe the error doesn't classify as sqlerror or oserrror?

user208567 Over a year ago

@Louis How are you running the above script? See amended section of post.

Nick Grealy Over a year ago

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

symcbean · Accepted Answer · 2012-02-01 12:01:05Z

1

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.

answered Feb 1, 2012 at 12:01

symcbean

48.4k6 gold badges63 silver badges99 bronze badges

Comments

J. Chomel · Accepted Answer · 2017-06-06 07:22:22Z

0

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

answered Jun 6, 2017 at 7:22

J. Chomel

8,44215 gold badges44 silver badges72 bronze badges

Collectives™ on Stack Overflow

Using sqlplus to programmatically check connection

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related