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

Answered by: Connor McDonald - Last updated: June 23, 2016 - 6:37 am UTC

Category: SQL*Plus - 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 we 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.

and you rated our response

  (2 ratings)

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

Reviews

June 23, 2016 - 3:23 am UTC

Reviewer: A reader



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

Followup  

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

June 23, 2016 - 7:05 am UTC

Reviewer: Ghassan

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