ORACLE 11 > cat error1.ksh
sqlplus / << failure
select to_number('A') from dual
/
exit
failure
echo "Return code after failure = $?"
ORACLE 11 >
When you run it, there is a failure in the SQL*Plus session but UNIX is not aware of this:
ORACLE 11 > ./error1.ksh
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 20 14:41:54 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> 2 select to_number('A') from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Return code after failure = 0
ORACLE 11 >
If you wish to tell UNIX there has been an error, you should add whenever sqlerror exit failure to the SQL*Plus session:
ORACLE 11 > cat error2.ksh
sqlplus / << failure
whenever sqlerror exit failure
select to_number('A') from dual
/
exit
failure
echo "Return code after failure = $?"
ORACLE 11 >
Then the return code will be non zero so UNIX will know about the error:
ORACLE 11 > ./error2.ksh
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 20 14:45:44 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> 2 select to_number('A') from dual
*
ERROR at line 1:
ORA-01722: invalid number
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Return code after failure = 1
ORACLE 11 >
No comments:
Post a Comment