Skip to Main Content
  • Questions

  • Handling ORA-12170: TNS:Connect timeout occurred & ORA-03114: not connected to ORACLE failures

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Harsha Vardhan.

Asked: June 22, 2016 - 1:52 pm UTC

Last updated: June 23, 2016 - 6:37 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

We had a scenario where the sqlplus connection failed with "ORA-12170: TNS:Connect timeout occurred" in one instance & "ORA-03114: not connected to ORACLE" in another instance while executing from a shell script, but in both the cases return code from sqlplus is 0 because of which the flow did not enter the failure handling block creating an issue.

So, can we handle these kind of errors and make sure that the return code is not 0 and it flows into error handling?

--Code snippet of the script
sqlplus user/pass@db_name <<EOF
whenever sqlerror exit sql.sqlcode
@sql_file
EOF

rc=$?
if [[ $rc -ne 0 ]]; then
handle error
fi

Thanks in advance!!!

and Connor said...

I've never been a fan of relying on the return code - I generally spool the output to a file and look for occurrences of ORA- or SP2- along the lines of:

sqlplus user/pass@db_name <<EOF 1>sql.out 2>&2
whenever sqlerror exit sql.sqlcode
@sql_file
EOF

if [ "`egrep '(ORA-|SP2-)' sql.out`" ] ; then
   echo "uh oh"
fi


Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, June 23, 2016 - 3:23 am UTC



I would agree too, but the main cause of concern here is that the return code of the sqlplus is 0 even when there is such an error because of which the failure steps are bypassed.

Why didnt the below statement fire
whenever sqlerror exit sql.sqlcode
Which would have the return code as non-zero and moved to failure handling.

Thanks!!!
Connor McDonald
June 23, 2016 - 6:37 am UTC

Can you give me an example ? I can't reproduce

[oracle@vbgeneric ~]$ sqlplus sys/oracle as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 23 02:36:00 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> host cat /tmp/x.sql
whenever sqlerror exit sql.sqlcode
declare
  e exception;
  pragma exception_init(e,-12170);
begin
  raise e;
end;
/

SQL> @/tmp/x.sql
declare
*
ERROR at line 1:
ORA-12170: TNS:Connect timeout occurred
ORA-06512: at line 5


Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@vbgeneric ~]$ echo $?
138


Error types : handling differ

Ghassan, June 23, 2016 - 7:05 am UTC

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#sthref889

For such type of error try using Exception clause in a block
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.