Recently, I sent a client a quick sample of how to pass a status code from
SQL*Plus to a Unix shell script, and realized that with that email, this
week's tip was nearly written.
First the shell script. It runs a
SQL script through SQL*Plus and tests the status being returned by SQL*Plus.
# test.sh
#
sqlplus scott/tiger
@test.sql
sqlstat=$?
if [ "$sqlstat"
= "0" ] ; then
echo
"success"
else
echo $sqlstat
fi
Then the SQL script. Note that the
table is dualx, which does not exist. The WHENEVER statement causes the
script to exit with a status of the Oracle error number when an error is
encountered.
-- test.sql
--
WHENEVER
SQLERROR EXIT SQL.SQLCODE
SELECT
* FROM dualx
/
EXIT
If I run test.sh, I get the
following result. The 174 is the result displayed by the script, which
is the remainder of the Oracle error number 942 divided by 256. (With a
little research, I found that the Unix status code can only handle
values less than 256.)
SQL*Plus:
Release 9.2.0.6.0 - Production on Thu Oct 19 14:04:39 2006
Copyright (c)
1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release
9.2.0.6.0 - 64bit Production
JServer Release
9.2.0.6.0 - Production
select * from
dualx
*
ERROR at line 1:
ORA-00942: table
or view does not exist
Disconnected
from Oracle9i Release 9.2.0.6.0 - 64bit Production
JServer Release
9.2.0.6.0 - Production
174
If I correct the SQL script,
changing dualx to dual, this is what happens.
SQL*Plus:
Release 9.2.0.6.0 - Production on Thu Oct 19 14:11:11 2006
Copyright (c)
1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release
9.2.0.6.0 - 64bit Production
JServer Release
9.2.0.6.0 - Production
D
-
X
Disconnected
from Oracle9i Release 9.2.0.6.0 - 64bit Production
JServer Release
9.2.0.6.0 - Production
success
The same approach works if you run a PL/SQL script. For this test, the
SQL script looked like this.
-- test.sql
--
WHENEVER
SQLERROR EXIT SQL.SQLCODE
BEGIN
RAISE_APPLICATION_ERROR (-20000, 'test');
END;
/
EXIT
The result of running the same shell script used previously with this
script produces a status displayed in Unix as 32, the remainder of 20000
from the exception the script raised divided by 256.
SQL*Plus: Release 9.2.0.6.0 -
Production on Fri Oct 20 08:46:35 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production
begin
*
ERROR at line 1:
ORA-20000: test
ORA-06512: at line 2
Disconnected from Oracle9i Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production
32
And to complete this tip on a positive note, running a PL/SQL block that
completes successfully, results in the following:
SQL*Plus: Release 9.2.0.6.0 -
Production on Fri Oct 20 08:48:42 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production
PL/SQL procedure successfully completed.
Disconnected from Oracle9i Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production
success
Note: This tip was tested using
Oracle9i on Solaris using the Korn shell..
Was this tip useful? Did you
find any errors? Do you have any suggestions? Do you care? Click
here for the tip feedback page. Thank you.
| 
