Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ghalia.

Asked: August 05, 2016 - 5:28 pm UTC

Last updated: August 17, 2016 - 12:05 pm UTC

Version: 11g / 12 c

Viewed 10K+ times! This question is

You Asked

Hi,

This is my question : I need to log in a table in my database every action be made on a script, even like sudden interruptions such us ctrl+c. The problem is that i don't know how to catch ORA-01013 ( which is the code for abrupt interruptions), because it isn't nor an error neither a warning..it is an 'informational error'. I tried to add in my script whenever sqlerror exit 1, i tried also WHENEVER OSERROR EXIT 1; and WHENEVER SQLERROR EXIT FAILURE;

--WHENEVER SQLERROR EXIT 1;
--WHENEVER SQLERROR EXIT WARNING 2;
--WHENEVER SQLWARNING EXIT 1;

--WHENEVER SQLWARNING STOP;
--WHENEVER SQLERROR=1013 exit 1;

without any success. I just want my script to be able to return an error code when a ctrl + c happens.

I am working in two environments oracle 11g and the other one is oracle 12c, so i need a solution that works for both of them..

Any solution for me pleaaaaase ? Need ur help !

Regards,

Ghalia

and Connor said...

The "WHENEVER" clause works for me, eg

# cat /tmp/x1.sql
select count(*)
from dba_Objects,dba_objects;


then I ran SQL Plus


SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 7 21:52:59 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> @/tmp/x1.sql
^Cselect count(*)
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation


So it doesnt exit, but now I add a WHENEVER clause

SQL> WHENEVER SQLERROR EXIT 1;
SQL> @/tmp/x1.sql
^Cfrom dba_Objects,dba_objects
     *
ERROR at line 2:
ORA-01013: user requested cancel of current operation


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








Rating

  (6 ratings)

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

Comments

More details

Ghalia BERRADA LAMINE, August 10, 2016 - 8:11 am UTC

Hi,
Thank you for your answer.
However i thnik i have to give you more details about what i need to do :
I have an sql script encapsulated in a ksh script. To execute the sql script i just execute the ksh one.
Now when i do ctrl+c during the execution of the ksh, it returns the ora-01013, but even with whenever in the sql script, it does not catch the error : i.e it doesn't log this error in my table in the database, because i found out that for Oracle, it isn't an error, it is just an informational message.
Hope that this will give you more information.
Looking forward t hearing from you.

Thanks.
Ghalia


Connor McDonald
August 12, 2016 - 3:33 am UTC

Maybe look at doing something like this then:

#!/bin/ksh

function error_handler {
if [ "`grep 'ORA-' /tmp/x1.log`" ] ; then
  echo Something went wrong
fi
    
}

trap error_handler INT

print "
connect system/oracle
@/tmp/x1.sql" | sqlplus system/oracle 1>/tmp/x1.log 2>&2

error_handler


More details

Ghalia BERRADA LAMINE, August 10, 2016 - 8:16 am UTC

I think i have to give you more details :i have an sql script encapsulated in a ksh one, so i just need to execute the ksh script.
During the execution of the ksh script, when i do ctrl + c it shows the same error ora-01013, but the problem is that it doesn't log it in my log table in oracle database, even if i am using whenever clause. I made some google search about this and i found out that ora-01013 is considered by Oracle as an informational message only, and not an error, maybe this is why it doesn't log it.
What i need is to know how can i log a line in my table when i have this error. How can i catch it please ?

Thanks.

Ghalia

More details ORA-01013

Ghalia BERRADA LAMINE, August 10, 2016 - 8:20 am UTC

I think i have to give you more details :i have an sql script encapsulated in a ksh one, so i just need to execute the ksh script.
During the execution of the ksh script, when i do ctrl + c it shows the same error ora-01013, but the problem is that it doesn't log it in my log table in oracle database, even if i am using whenever clause. I made some google search about this and i found out that ora-01013 is considered by Oracle as an informational message only, and not an error, maybe this is why it doesn't log it.
What i need is to know how can i log a line in my table when i have this error. How can i catch it please ?

Thanks.

Ghalia

Dynamic Logs

Ghalia BERRADA LAMINE, August 16, 2016 - 3:03 pm UTC

Hi,

Thank you for your answer.
However, my file log is generated dynamically as soon as i execute my ksh script with the date and the hour in the name.

Any idea please ?

Ghalia
Chris Saxon
August 17, 2016 - 1:19 am UTC

I dont like using the return code, because for me, what matters is, was there a ORA- or SP2- error.

Dynamic log files are no prob, eg just set it at the start of the script

logfile=/tmp/log.`date`

and then

sqlplus ... 1>$logfile 2>&1

etc

Last try

Ghalia BERRADA LAMINE, August 16, 2016 - 4:50 pm UTC

I tried to adapt your solution to my situation, i am now doing something like this :

#!/bin/ksh

function error_handler {
if [ "`grep 'ORA-' /apps/syb/Data/Habilitation_Oracle.log`" ] ; then
${RetSql}=1
fi
}
trap error_handler INT

$ORACLE_HOME/bin/sqlplus $ORA_LOGIN/$ORA_PASS@$ORACLE_SID.world @/apps/syb/Data/sql/LOG_INIT_HABILITATION.sql 'En cours'
$ORACLE_HOME/bin/sqlplus $ORA_LOGIN/$ORA_PASS@$ORACLE_SID.world @/apps/syb/Data/sql/SC_MAJ_HABILITATION.sql

RetSql=$?

error_handler



if [ ${RetSql} -eq 0 ]
then
echo ${RetSql}
$ORACLE_HOME/bin/sqlplus $ORA_LOGIN/$ORA_PASS@$ORACLE_SID.world @/apps/syb/Data/sql/LOG_MAJ_HABILITATION.sql 'OK'
else
echo ${RetSql}
$ORACLE_HOME/bin/sqlplus $ORA_LOGIN/$ORA_PASS@$ORACLE_SID.world @/apps/syb/Data/sql/LOG_MAJ_HABILITATION.sql 'KO'
exit 1
fi

AND it is working !! but it shows me those 2 errors :

error_handler[2]:=1 : not found
error_handler[2]: 1=1 : not found


WHat do you think please ?
Regards,

Ghalia

Ghalia BERRADA LAMINE, August 17, 2016 - 8:33 am UTC

I tried to adapt your solution to my situation, i am now doing something like this :

#!/bin/ksh

function error_handler {
if [ "`grep 'ORA-' /tmp/x1.log`" ] ; then
${RetSql}=1
fi
}
trap error_handler INT

$ORACLE_HOME/bin/sqlplus $ORA_LOGIN/$ORA_PASS@$ORACLE_SID.world path/myscript.sql 'running'
$ORACLE_HOME/bin/sqlplus $ORA_LOGIN/$ORA_PASS@$ORACLE_SID.world path/myscript2.sql

RetSql=$?

error_handler



if [ ${RetSql} -eq 0 ]
then
echo ${RetSql}
$ORACLE_HOME/bin/sqlplus $ORA_LOGIN/$ORA_PASS@$ORACLE_SID.world path/myscript3.sql 'OK'
else
echo ${RetSql}
$ORACLE_HOME/bin/sqlplus $ORA_LOGIN/$ORA_PASS@$ORACLE_SID.world path/myscript3.sql 'KO'
exit 1
fi

AND it is working !! but it shows me those 2 errors :

error_handler[2]:=1 : not found
error_handler[2]: 1=1 : not found


What do you think please ?
Regards,

Ghalia
Connor McDonald
August 17, 2016 - 12:05 pm UTC

see my previous updates
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.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.